<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Renewal_tracker  extends Action_controller{	
	public function __construct(){
		parent::__construct('renewal_tracker');
		$this->collect_base_info();
	}
	
	// ================ LOAD PAGE QUICK LINK =======================
	public function index(){
		$data['quick_link']    = $this->quick_link;
		$this->load->view("$this->control_name/manage",$data);
	}
	
	//================= LOAD TABEL DATE WITH FILTERS ======================
	public function search(){
		$start_date          = date('Y-m-d', strtotime($this->input->post('start_date')));
		$end_date            = date('Y-m-d', strtotime($this->input->post('end_date')));

		// WILL GET THE RENEWALS TRACKERED DATAS 
		$renewal_tracker_qry = 'SELECT * FROM (SELECT chr.prime_health_renewals_id,ch.login_date,ch.issued_date,ch.policy_from_date,chr.policy_number,chr.proposer_name,cc.company_name,cp.product_name,ch.dob,ch.age,chrl.renewal_net_premium AS premium,pm.payment_mode,si.sum_insured,chr.due_date,e.employee_name,CONCAT(e1.employee_code," - ",e1.employee_name) AS level_1_reporting_person,CONCAT(e2.employee_code," - ",e2.employee_name) AS level_2_reporting_person,chr.ncd,chr.renewed_date,chr.trans_updated_by,chr.trans_updated_date,chr.expected_date,rs.renewal_status,cs.health_call_status AS call_status,sd.sub_disposition,cl.remarks,hc.last_claim_date,hc.last_claim_amount,ps.paid_status AS last_claim_status,hc.total_claim_amount,hc.total_claim_count,"ACHIEVED" AS status FROM cw_health_renewals_log chrl JOIN cw_health_renewals chr ON chrl.prime_health_renewals_id = chr.prime_health_renewals_id LEFT JOIN cw_health ch ON chr.prime_health_id = ch.prime_health_id LEFT JOIN cw_health_company cc ON cc.prime_health_company_id = chr.company LEFT JOIN cw_health_product cp ON cp.prime_health_product_id = chr.product LEFT JOIN cw_payment_mode pm ON pm.prime_payment_mode_id = chr.payment_mode LEFT JOIN cw_sum_insured si ON si.prime_sum_insured_id = chr.sum_insured LEFT JOIN cw_employees e ON e.employee_code = chr.employee_code LEFT JOIN cw_health_renewal_status rs ON rs.prime_renewal_status_id = chr.renewal_status LEFT JOIN cw_health_call_status cs ON cs.prime_health_call_status_id = chr.call_status LEFT JOIN cw_sub_disposition sd ON sd.prime_sub_disposition_id = chr.sub_disposition LEFT JOIN (SELECT cl1.* FROM cw_health_renewals_call_log cl1 JOIN (SELECT prime_health_renewals_id,MAX(prime_health_renewals_call_log_id) AS last_log_id FROM cw_health_renewals_call_log WHERE trans_status = 1 GROUP BY prime_health_renewals_id) cl2 ON cl1.prime_health_renewals_call_log_id = cl2.last_log_id) cl ON cl.prime_health_renewals_id = chr.prime_health_renewals_id LEFT JOIN (SELECT health_renewal_id,MAX(CASE WHEN claim_int_date IS NOT NULL THEN claim_int_date ELSE date_of_admission END) AS last_claim_date,MAX(paid_amount) AS last_claim_amount,SUM(paid_amount) AS total_claim_amount,COUNT(prime_health_claim_id) AS total_claim_count,MAX(paid_status) AS paid_status FROM cw_health_claim WHERE trans_status = 1 GROUP BY health_renewal_id) hc ON hc.health_renewal_id = chr.prime_health_renewals_id LEFT JOIN cw_paid_status ps ON ps.prime_paid_status_id = hc.paid_status LEFT JOIN cw_employees e1 ON e1.employee_code = chr.level_1_reporting_person LEFT JOIN cw_employees e2 ON e2.employee_code = chr.level_2_reporting_person WHERE chrl.trans_status = 1 AND chrl.renewal_from_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND chrl.from_health != 1 AND chrl.trans_created_by != 1 UNION
		SELECT chr.prime_health_renewals_id,ch.login_date,ch.issued_date,ch.policy_from_date,chr.policy_number,chr.proposer_name,cc.company_name,cp.product_name,ch.dob,ch.age,chr.net_premium AS premium,pm.payment_mode,si.sum_insured,chr.due_date,e.employee_name,CONCAT(e1.employee_code," - ",e1.employee_name),CONCAT(e2.employee_code," - ",e2.employee_name),chr.ncd,chr.renewed_date,chr.trans_updated_by,chr.trans_updated_date,chr.expected_date,rs.renewal_status,cs.health_call_status,sd.sub_disposition,cl.remarks,hc.last_claim_date,hc.last_claim_amount,ps.paid_status,hc.total_claim_amount,hc.total_claim_count,"PENDING" AS status FROM cw_health_renewals chr LEFT JOIN cw_health ch ON chr.prime_health_id = ch.prime_health_id LEFT JOIN cw_health_company cc ON cc.prime_health_company_id = chr.company LEFT JOIN cw_health_product cp ON cp.prime_health_product_id = chr.product LEFT JOIN cw_payment_mode pm ON pm.prime_payment_mode_id = chr.payment_mode LEFT JOIN cw_sum_insured si ON si.prime_sum_insured_id = chr.sum_insured LEFT JOIN cw_employees e ON e.employee_code = chr.employee_code LEFT JOIN cw_health_renewal_status rs ON rs.prime_renewal_status_id = chr.renewal_status LEFT JOIN cw_health_call_status cs ON cs.prime_health_call_status_id = chr.call_status LEFT JOIN cw_sub_disposition sd ON sd.prime_sub_disposition_id = chr.sub_disposition LEFT JOIN (SELECT cl1.* FROM cw_health_renewals_call_log cl1 JOIN (SELECT prime_health_renewals_id,MAX(prime_health_renewals_call_log_id) AS last_log_id FROM cw_health_renewals_call_log WHERE trans_status = 1 GROUP BY prime_health_renewals_id) cl2 ON cl1.prime_health_renewals_call_log_id = cl2.last_log_id) cl ON cl.prime_health_renewals_id = chr.prime_health_renewals_id LEFT JOIN (SELECT health_renewal_id,MAX(CASE WHEN claim_int_date IS NOT NULL THEN claim_int_date ELSE date_of_admission END) AS last_claim_date,MAX(paid_amount) AS last_claim_amount,SUM(paid_amount) AS total_claim_amount,COUNT(prime_health_claim_id) AS total_claim_count,MAX(paid_status) AS paid_status FROM cw_health_claim WHERE trans_status = 1 GROUP BY health_renewal_id) hc ON hc.health_renewal_id = chr.prime_health_renewals_id LEFT JOIN cw_paid_status ps ON ps.prime_paid_status_id = hc.paid_status LEFT JOIN cw_employees e1 ON e1.employee_code = chr.level_1_reporting_person LEFT JOIN cw_employees e2 ON e2.employee_code = chr.level_2_reporting_person WHERE chr.trans_status = 1 AND chr.due_date BETWEEN "'.$start_date.'" AND "'.$end_date.'") x GROUP BY prime_health_renewals_id';

		$info  = $this->db->query("CALL sp_a_run ('SELECT','".$renewal_tracker_qry."')");
		$rewnwals_tracker_qry_rslt = $info->result_array();
		$info->next_result();
		if($rewnwals_tracker_qry_rslt){
			echo json_encode(array('success' => TRUE,'message' => "Renewal Tracker Details",'renewal_tracker_report' => $rewnwals_tracker_qry_rslt));
		}else{
			echo json_encode(array('success' => FALSE,'message' => "No Data Available",'renewal_report' => array()));
		}
				
	}
}
?>