File: /home/cafsindia/login_cafsindia_com/application/controllers/Health_renewals_bk21JAN2025.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Health_renewals extends Action_controller{
public function __construct(){
parent::__construct('health_renewals');
$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;
$this->load->view("$this->control_name/manage",$data);
}
//LOAD TABEL WITH FILTERS
public function search(){
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$order = $this->input->post('order');
$order_col = $this->input->post('columns');
$search = $this->input->post('search');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$order_col = $order_col[$column]['data'];
$search = trim($search['value']);
$search_query = str_replace("@SELECT@",$this->select_query,$this->base_query);
//FOR SHOW ONLY FROM TODAY DATE TO NEXT 30 DAYS RENEWAL DATA
$today_date = date("Y-m-d");
$due_check_date = date("Y-m-d", strtotime("+90 days",strtotime($today_date)));
//DUE DATE AND NCD STATIC SEARCH CODE
$ncd_due_from_date = $this->input->post('start_date');
$ncd_due_to_date = $this->input->post('end_date');
$ncd_due = (int)$this->input->post('ncd_due');
$ncd_due_date_filter = "";
//not added in superadmin login
// if((int)$this->logged_role !== 1){
if($ncd_due === 2 && $ncd_due_from_date && $ncd_due_to_date){
$ncd_due_from_date = date("Y-m-d G:i:s", strtotime($ncd_due_from_date));
$ncd_due_to_date = date("Y-m-d G:i:s", strtotime($ncd_due_to_date));
//NCD AND DUE DATE WHERE CONDITION
$ncd_due_date_filter = " and ($this->prime_table.ncd BETWEEN '$ncd_due_from_date' and '$ncd_due_to_date' or $this->prime_table.ncd IS NULL)";
//this command where conditions temporarily commended but it is required
// and $this->prime_table.due_date <= '$due_check_date'
}else
if($ncd_due === 1 && $ncd_due_from_date){
$ncd_due_from_date = date("Y-m-d", strtotime($ncd_due_from_date));
$ncd_due_to_date = date("Y-m-d", strtotime($ncd_due_to_date));
//NCD AND DUE DATE WHERE CONDITION
// if((int)$this->logged_role === 1){
$ncd_due_date_filter = " and ($this->prime_table.due_date BETWEEN '$ncd_due_from_date' and '$ncd_due_to_date')";
//this command where conditions temporarily commended but it is required
// or $this->prime_table.due_date <= '$due_check_date'
// }
// else{
// $ncd_due_date_filter = " and $this->prime_table.due_date BETWEEN '$ncd_due_from_date' and '$due_check_date'";
// }
}else{
//NCD AND DUE DATE WHERE CONDITION
$ncd_due_date_filter = " and $this->prime_table.due_date <= '$due_check_date' and $this->prime_table.Payment_mode not in (7)";
}
// }
if($order_col === "prime_health_renewals_id"){
$order_col = "due_date";
}
//ADDED BASIC,FILTER,COMMON QUERY HERE
$role_condition = "";
if($this->role_condition){
$role_condition = $this->role_condition;
}
$fliter_query = "";
foreach($this->fliter_list as $fliter){
$label_id = $fliter['label_id'];
$label_name = $fliter['label_name'];
$field_isdefault = (int)$fliter['field_isdefault'];
$array_list = $fliter['array_list'];
$field_type = (int)$fliter['field_type'];
if($field_isdefault === 1){
$column_name = $this->prime_table .".$label_id";
$search_val = $this->input->post("$label_id");
if($search_val){
if($field_type === 4){
$search_val = date('Y-m-d',strtotime($search_val));
$fliter_query .= " and $column_name = '$search_val'";
}else
if(($field_type === 5) || ($field_type === 7)){
$search_val = trim(implode("','",$search_val));
$fliter_query .= " and $column_name in ('$search_val')";
}else
if($field_type === 13){
$search_val = date('Y-m-d H:i:s',strtotime($search_val));
$fliter_query .= " and $column_name = '$search_val'";
}else{
$fliter_query .= " and $column_name LIKE '$search_val%'";
}
}
}
}
$common_search = "";
if($search){
foreach($this->form_info as $setting){
$prime_form_id = $setting->prime_form_id;
$field_type = (int)$setting->field_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$pick_list_type = $setting->pick_list_type;
$input_view_type = (int)$setting->input_view_type;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$label_id = strtolower(str_replace(" ","_",$setting->label_name));
$field_isdefault = (int)$setting->field_isdefault;
if($field_isdefault === 1){
if(($input_view_type === 1) || ($input_view_type === 2)){
$search_label = "$this->prime_table.$label_id";
$search_val = "";
if($field_type === 4){ // having issues in date search
if(strtotime($search)){
$search_val = date('Y-m-d',strtotime($search));
$common_search .= ' or '. $search_label .' like "'.$search_val.'%"';
}
}else
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$result = array_filter($this->master_pick[$label_id], function ($item) use ($search) {
if (stripos($item, $search) !== false) {
return true;
}
return false;
});
if($result){
$pick_key = implode('","',array_keys($result));
$common_search .= ' or '. $search_label .' in("'.$pick_key.'")';
}
}else
if($field_type === 10){ // having issues in date search
$search_label = "$this->prime_table.$label_id";
if(strtotime($search)){
$common_search .= ' or '. $search_label .' like "'.$search.'%"';
}
}
else{
$common_search .= ' or '. $search_label .' like "'.$search.'%"';
}
}
}
}
if($common_search){
$common_search = ltrim($common_search,' or ');
$common_search = " and ($common_search)";
$common_search = str_replace("(,","(",$common_search);
$common_search = str_replace("()","(0)",$common_search);
}
}
$count_all_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$search_total = $this->db->query($count_all_query);
$search_total_info = $search_total->result();
$total_count = $search_total_info[0]->allcount;
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query .= " where $this->prime_table.trans_status = 1 $role_condition $fliter_query $common_search$ncd_due_date_filter ORDER BY $order_col $order_sor";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " where $this->prime_table.trans_status = 1 $role_condition $fliter_query $common_search$ncd_due_date_filter";
$search_query .= " ORDER BY $order_col $order_sor";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
//echo "search_query :: \n$search_query\n";
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
//LOAD MODEL PAGE VIEW WITH DATA
public function view($form_view_id=-1){
//VIEW, FORM INPUT
$data['view_info'] = $this->view_info;
$data['form_info'] = $this->form_info;
$data['role_based_condition'] = $this->role_based_condition;//for label name readonly options
//VIEW DATA
$base_query = str_replace("@SELECT@",$this->view_select,$this->base_query);
$view_query = $base_query ." where $this->prime_table.$this->prime_id = $form_view_id and $this->prime_table.trans_status = 1";
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_query')");
$view_result = $view_data->result();
$view_data->next_result();
$data['form_view'] = $view_result[0];
//AUTO COMPLTE,PICK LIST AND CONDITION
foreach($this->form_info as $from){
$prime_form_id = (int)$from->prime_form_id;
$field_type = (int)$from->field_type;
$pick_table = $from->pick_table;
$auto_prime_id = $from->auto_prime_id;
$auto_dispaly_value = $from->auto_dispaly_value;
$label_id = $from->label_name;
if($field_type === 9){
if($view_result[0]){
$get_value = $view_result[0]->$label_id;
if($get_value){
$pick_query = 'select '.$auto_dispaly_value.' from '.$pick_table.' where '.$auto_prime_id.' = "'.$get_value.'" and trans_status = 1';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$this->all_pick[$prime_form_id] = $pick_result[0]->$auto_dispaly_value;
}
}
}
}
$data['all_pick'] = $this->all_pick;
$data['condition_list'] = $this->condition_list;
$view_qry = 'select * from cw_form_view_setting where prime_view_module_id = "'.$this->control_name.'" and form_view_type = "3" and trans_status = 1';
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_qry')");
$view_result = $view_data->result();
$view_data->next_result();
$row_view_list = array();
foreach($view_result as $view){
$prime_form_view_id = $view->prime_form_view_id;
$row_set_data = $this->get_row_set_data($prime_form_view_id,$form_view_id);
$row_view_list[$prime_form_view_id] = $row_set_data;
}
$data['row_view_list'] = $row_view_list;
$this->load->view("$this->control_name/form",$data);
}
//SAVE MODEL DATA TO DATA BASE
public function save(){
$unq_chk = array();
$prime_qry_key = "";
$prime_qry_value = "";
$prime_upd_query = "";
$cf_qry_key = "";
$cf_qry_value = "";
$cf_upd_query = "";
$cf_has = false;
$form_id = (int)$this->input->post($this->prime_id);
$form_post_data = array();
$renewals_log = array();
$renewals_log['prime_health_renewals_id'] = $form_id;
foreach($this->form_info as $setting){
$field_type = $setting->field_type;
$input_view_type = (int)$setting->input_view_type;
$label_id = strtolower(str_replace(" ","_",$setting->label_name));
$field_isdefault = $setting->field_isdefault;
$unique_field = (int)$setting->unique_field;
$view_name = $setting->view_name;
if((int)$field_type === 7){
$multi_name = $label_id."[]";
$value = trim(implode(",",$this->input->post($multi_name)));
}else{
$value = trim($this->input->post($label_id));
}
if((int)$field_type === 4){
$value = date('Y-m-d',strtotime($value));
}else
if((int)$field_type === 8){//textbox only
$value = str_replace('"',"xdbquot",$value);
$value = str_replace("'","xquot",$value);
$value = str_replace("&","xxamp",$value);
}else
if((int)$field_type === 13){
$value = date('Y-m-d H:i:s',strtotime($value));
}
if($label_id === 'issuance_status'){
$issuence_sts = (int)$value;
}
if(($input_view_type === 1) || ($input_view_type === 2)){
if((int)$field_isdefault === 1){
$prime_qry_key .= $label_id.",";
$prime_qry_value .= '"'.$value.'",';
$prime_upd_query .= $label_id.' = "'.$value.'",';
if($unique_field === 1){
$prime_unq_chk = $label_id.'= "'.$value.'"';
$query = "select count(*) as rslt_count from $this->prime_table where $this->prime_id != $form_id and $prime_unq_chk";
$unq_chk[] = array('label_id'=>$label_id,'view_name'=>$view_name,'query'=>$query,);
}
}
$renewals_log[$label_id] = $value;
}
}
$rslt_count = 0;
$can_process = array();
foreach($unq_chk as $unq_rslt){
$query = $unq_rslt['query'];
$label_id = $unq_rslt['label_id'];
$view_name = $unq_rslt['view_name'];
$unq_info = $this->db->query("CALL sp_a_run ('RUN','$query')");
$unq_result = $unq_info->result();
$unq_info->next_result();
if($unq_result){
$rslt_count = (int)$unq_result[0]->rslt_count;
if($rslt_count !== 0){
$can_process[] = $view_name." already exist";
}
}
}
if(count($can_process) > 0){
$can_process = array_values($can_process);
$can_process = implode(",<br/>", $can_process);
echo json_encode(array('success' => false, 'message' => $can_process,));
}else{
$created_on = date("Y-m-d H:i:s");
if((int)$form_id === 0){
$prime_qry_key .= "trans_created_by,trans_created_date";
$prime_qry_value .= '"'.$this->logged_id.'",'.'"'.$created_on.'"';
$prime_insert_query = "insert into $this->prime_table ($prime_qry_key) values ($prime_qry_value)";
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
echo json_encode(array('success' => TRUE, 'message' => "Successfully added", 'insert_id' => $insert_id));
}else{
//renewals log function
$this->renewals_log($form_id,$renewals_log);
$prime_upd_query .= 'trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$prime_update_query = 'UPDATE '. $this->prime_table .' SET '. $prime_upd_query .' WHERE '. $this->prime_id .' = "'. $form_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')");
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated",'insert_id' => $form_id));
}
}
}
//RENEWALS TABLE LOG FOR EVERY UPDATE OF THE RENEWALS
public function renewals_log($prime_id,$renewals_log){
$created_on = date("Y-m-d H:i:s");
$logged_id = $this->logged_id;
$renewals_label_name = array_keys($renewals_log);
$renewals_label_value = implode(",",$renewals_label_name);
$renewals_qry = "select $renewals_label_value from cw_health_renewals where prime_health_renewals_id = $prime_id";
$renewals_data = $this->db->query("CALL sp_a_run ('SELECT','$renewals_qry')");
$renewals_result = $renewals_data->result_array();
$renewals_data->next_result();
$fin_renewals_result = $renewals_result[0];
$policy_no = $fin_renewals_result['current_policy_no'];
$renewal_data_dif = array_diff_assoc($fin_renewals_result,$renewals_log);
$prime_qry_value = "";
$prime_qry_key = "prime_health_renewals_id,policy_no,label_name,old_value,new_value,trans_created_by,trans_created_date";
foreach($renewal_data_dif as $col_key=>$col_value){
$old_label_val = $fin_renewals_result[$col_key];
$new_label_val = $renewals_log[$col_key];
if(($old_label_val != $new_label_val) && ($old_label_val !=='0.00' && $new_label_val !='') && ($old_label_val !=='0000-00-00' && $new_label_val !='') && $new_label_val !== '1970-01-01'){
$prime_qry_value .= "(\"$prime_id\",\"$policy_no\",\"$col_key\",\"$old_label_val\",\"$new_label_val\",\"$logged_id\",\"$created_on\"),";
}
}
if(!empty($prime_qry_value)){
$prime_qry_value = rtrim($prime_qry_value,',');
$prime_insert_query = "insert into cw_health_renewals_upd_log ($prime_qry_key) values $prime_qry_value";
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
}
}
//UPDATE STATUS TO DELETE IN MODULE PRIMARY TABLE
public function delete(){
$delete_ids = implode(",",$this->input->post('delete_ids'));
$can_process = TRUE;
$delete_status = FALSE;
if($this->check_delete_status()){
$delete_status = TRUE;
$check_table_query = 'SELECT GROUP_CONCAT(prime_module_id) as prime_module_id,GROUP_CONCAT(label_name) as label_name from cw_form_setting WHERE pick_table = "'. $this->prime_table .'" and trans_status = 1 ';
$check_table_info = $this->db->query("CALL sp_a_run ('SELECT','$check_table_query')");
$check_table_rlst = $check_table_info->row();
$check_table_info->next_result();
if($check_table_rlst->prime_module_id){
$prime_module_id = explode(",",$check_table_rlst->prime_module_id);
$label_name = explode(",",$check_table_rlst->label_name);
$i = 0;
$select_table = '';
$select_label = '';
$select_trans_status = '';
$select_where = '';
foreach($prime_module_id as $check_modules){
$table_name = "cw_".$check_modules;
$table_rename = $table_name."_$i";
$select_table .= "$table_rename.$label_name[$i],";
$select_label .= " $table_name $table_rename,";
if((int)$i === 0){
$select_trans_status .= "( $table_rename.trans_status = 1";
$select_where .= " and ($table_rename.$label_name[$i] in ($delete_ids)";
}else{
$select_trans_status .= " and $table_rename.trans_status = 1";
$select_where .= " or $table_rename.$label_name[$i] in ($delete_ids)";
}
$i++;
}
$select_trans_status .= ")";
$select_where .= ")";
$select_table = rtrim($select_table,',');
$select_label = rtrim($select_label,',');
$check_module_query .= 'SELECT '.$select_table.' from '.$select_label.' WHERE '.$select_trans_status.' '.$select_where.' LIMIT 0,1';
$check_module_info = $this->db->query("CALL sp_a_run ('SELECT','$check_module_query')");
$values_count = $check_module_info->num_rows();
$check_module_info->next_result();
if((int)$values_count > 0){
$can_process = False;
$delete_status = False;
}
}
if($delete_status){
$delete_query = 'DELETE FROM '. $this->prime_table .' WHERE '. $this->prime_id .' in ('. $delete_ids .')';
if($this->db->query("CALL sp_a_run ('RUN','$delete_query')")){
$row_set_query = 'SELECT form_view_label_name from cw_form_view_setting where form_view_type = "3" and prime_view_module_id = "'. $this->control_name .'" and trans_status = 1';
$row_set_info = $this->db->query("CALL sp_a_run ('SELECT','$row_set_query')");
$row_count = (int)$row_set_info->num_rows();
$row_set_info->next_result();
if($row_count !== 0){
$row_set_result = $row_set_info->result();
$delete_table_name = '';
$delete_table_condition = '';
foreach($row_set_result as $row_set){
$row_set_table_name = "cw_".$this->control_name."_".$row_set->form_view_label_name;
$delete_table_name .= "$row_set_table_name,";
$delete_table_condition .= " $row_set_table_name.$this->prime_id in ('$delete_ids') and";
}
$delete_table_name = rtrim($delete_table_name,',');
$delete_table_condition = rtrim($delete_table_condition,'and');
$delete_row_set_query = 'DELETE FROM '. $delete_table_name .' WHERE '. $delete_table_condition.'';
$this->db->query("CALL sp_a_run ('RUN','$delete_row_set_query')");
}
$can_process = False;
}
}
}
if($can_process){
$created_on = date("Y-m-d H:i:s");
$prime_upd_query .= 'trans_deleted_by = "'. $this->logged_id .'",trans_deleted_date = "'.$created_on.'"';
$prime_update_query = 'UPDATE '. $this->prime_table .' SET trans_status = 0,'. $prime_upd_query .' WHERE '. $this->prime_id .' in ('. $delete_ids .')';
if($this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')")){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to delete"));
}
}else
if($delete_status){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}else{
$modules = ucwords($check_table_rlst->prime_module_id);
echo json_encode(array('success' => FALSE, 'message' => "Unable to delete, This value is already used in $modules modules"));
}
}
//CHECK UNIQUE FIELD STATUS
public function check_delete_status(){
$check_delete_query = 'SELECT GROUP_CONCAT(unique_field) as unique_field from cw_form_setting WHERE prime_module_id = "'. $this->control_name .'" and trans_status = 1 ';
$check_delete_info = $this->db->query("CALL sp_a_run ('SELECT','$check_delete_query')");
$check_delete_rlst = $check_delete_info->row();
$check_delete_info->next_result();
$unique_info = explode(",",$check_delete_rlst->unique_field);
if(in_array('1', $unique_info)){
return TRUE;
}else{
return FALSE;
}
}
//UPDATE STATUS TO DELETE FOR UPLOAD FILES or DOCUMENTS
public function remove_file(){
$prime_id_val = $this->input->post('prime_id_val');
$is_defult = (int)$this->input->post('is_defult');
$input_name = $this->input->post('input_name');
$table_name = '';
if($is_defult === 1){
$table_name = $this->prime_table;
}else
if($is_defult === 2){
$table_name = $this->cf_table;
}
if($table_name){
$created_on = date("Y-m-d H:i:s");
$set_query = $input_name .' = "" ,trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$update_query = 'UPDATE '.$table_name .' SET '. $set_query .' WHERE '. $this->prime_id .' = "'. $prime_id_val .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to process your request"));
}
}
public function rowset_remove_file(){
$table_name = $this->input->post('table_name_set');
$row_id = (int)$this->input->post('row_id');
$input_name = $this->input->post('input_name');
$view_id = (int)$this->input->post('view_id');
$primes_id = (int)$this->input->post('primes_id');
$remove_cw = str_replace("cw_","",$table_name);
$primary_id = "prime_".$remove_cw."_id";
if($table_name){
$created_on = date("Y-m-d H:i:s");
$set_query = $input_name .' = "" ,trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$update_query = 'UPDATE '.$table_name .' SET '. $set_query .' WHERE '.$primary_id.' = "'. $row_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
$row_set_edit = $this->get_row_set_data($view_id,$primes_id);
$div_id = $row_set_edit['div_id'];
$table_id = $row_set_edit['table_id'];
$row_set_view = $row_set_edit['row_set_view'];
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated",'div_id' => $div_id, 'table_id' => $table_id,'row_set_view'=>$row_set_view));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to process your request"));
}
}
//IMPORT FILE VIEW INFORMATION
public function import(){
$data['module_id'] = $this->control_name;
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "'.$this->control_name.'" and trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_format_drop[""] = "---- Excel Format ----";
foreach($excel_result as $excel){
$prime_excel_format_id = $excel->prime_excel_format_id;
$excel_name = $excel->excel_name;
$excel_format_drop[$prime_excel_format_id] = $excel_name;
}
$data['excel_format_drop'] = $excel_format_drop;
$this->load->view("$this->control_name/import",$data);
}
public function get_policy_covered_year(){
$policy_from = $this->input->post('policy_from');
$policy_to = $this->input->post('policy_to');
$policy_from = new DateTime($policy_from);
$policy_from = $policy_from->format("Y-m-d");
$policy_to = new DateTime($policy_to);
$policy_to = $policy_to->modify("+1 day");
$policy_to = $policy_to->format("Y-m-d");
$date_parts1 = explode("-", $policy_from);
$date_parts2 = explode("-", $policy_to);
$start_date = gregoriantojd($date_parts1[1], $date_parts1[2], $date_parts1[0]);
$end_date = gregoriantojd($date_parts2[1], $date_parts2[2], $date_parts2[0]);
$diff = abs($end_date - $start_date);
$diff = $diff+1;
if((int)$diff === 365){
$covered_year = 1;
}else{
$covered_year = floor($diff / 365.25);
}
echo json_encode(array('success' => true, 'diff' => $diff ,'covered_year' => $covered_year));
}
//GET MEMBERS COVERED DATA
public function members_save($prime_id){
$prime_health_renewals_mem_log_id = (int)$this->input->post('prime_health_renewals_insured_log_id');
$prime_health_renewals_id = (int)$this->input->post('prime_health_renewals_id');
$insured_name = $this->input->post('insured_name');
$relationship = $this->input->post('relationship');
$insured_dob = date("Y-m-d",strtotime($this->input->post('insured_dob')));
$insured_gender = $this->input->post('insured_gender');
$ped = $this->input->post('ped');
$mem_sum_insured = $this->input->post('mem_sum_insured');
$tpa_card_no = $this->input->post('tpa_card_no');
$premium = $this->input->post('premium');
$plus_on_premium = $this->input->post('plus_on_premium');
$net_premium = $this->input->post('net_premium');
$gst = $this->input->post('gst');
$total_premium = $this->input->post('total_premium');
$ped_remarks = $this->input->post('ped_remarks');
$pre_disease_cover = $this->input->post('pre_disease_cover');
$disease_note = $this->input->post('disease_note');
$created_on = date("Y-m-d H:i:s");
$health_renewal_query = str_replace("@SELECT@","*",$this->base_query);
$health_renewal_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.$this->prime_id = '$prime_id'";
$health_renewal_info = $this->db->query('CALL sp_a_run ("SELECT","'.$health_renewal_query.'")');
$health_renewal_result = $health_renewal_info->result();
$health_renewal_info->next_result();
$prime_health_id = $health_renewal_result[0]->prime_health_id;
$ins_health_renewal_mem_rslt = "";
if($prime_health_renewals_mem_log_id === 0){
$ins_health_renewal_mem_qry = 'insert into cw_health_renewals_insured_detail_log (prime_health_id,prime_health_renewals_id,insured_name,relationship,insured_dob,insured_gender,ped,mem_sum_insured,tpa_card_no,premium,plus_on_premium,net_premium,gst,total_premium,pre_disease_cover,ped_remarks,disease_note,trans_created_by,trans_created_date) VALUES ("'.$prime_health_id.'","'.$prime_id.'","'.$insured_name.'","'.$relationship.'","'.$insured_dob.'","'.$insured_gender.'","'.$ped.'","'.$mem_sum_insured.'","'.$tpa_card_no.'","'.$premium.'","'.$plus_on_premium.'","'.$net_premium.'","'.$gst.'","'.$total_premium.'","'.$pre_disease_cover.'","'.$ped_remarks.'","'.$disease_note.'","'.$this->logged_id.'","'.$created_on.'")';
$ins_health_renewal_mem_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_health_renewal_mem_qry')");
$ins_health_renewal_mem_rslt = $ins_health_renewal_mem_info->result();
$ins_health_renewal_mem_info->next_result();
$mem_ins_id = $ins_health_renewal_mem_rslt[0]->ins_id;
$renewal_call_mem_upd_qry = 'UPDATE cw_health_renewals_insured_detail_log SET mem_sum_insured = "'.$mem_sum_insured.'",premium = "'.$premium.'",plus_on_premium = "'.$plus_on_premium.'",net_premium = "'.$net_premium.'",gst = "'.$gst.'",total_premium = "'.$total_premium.'",trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'" where prime_health_renewals_id = "'.$prime_health_renewals_id.'"';
//HEALTH RENEWALS TABLE UPD QUERY
$renewal_call_mem_upd_qry = 'UPDATE cw_health_renewals SET sum_insured = "'.$mem_sum_insured.'",premium = "'.$premium.'",plus_on_premium = "'.$plus_on_premium.'",net_premium = "'.$net_premium.'",gst = "'.$gst.'",total_premium = "'.$total_premium.'",trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'" where prime_health_renewals_id = "'.$prime_health_renewals_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$renewal_call_mem_upd_qry')");
// $renewals_log_qry = 'UPDATE cw_health_renewals_log SET renewal_sum_insured = "'.$mem_sum_insured.'",renewal_premium_amount = "'.$net_premium.'",renewal_total_premium = "'.$total_premium.'",trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'" where prime_health_renewals_id = "'.$prime_health_renewals_id.'" order by prime_health_renewals_id DESC LIMIT 1';
// $this->db->query("CALL sp_a_run ('RUN','$renewals_log_qry')");
$members_covered = $this->members_covered_sum($prime_id);
// die;
// $policy_type = (int)$members_covered['policy_type'];
$sum_insured = (int)$members_covered['sum_insured'];
$over_all_total_premium = $members_covered['total_premium'];
$gst = $members_covered['gst'];
$renewal_mem_cover_list = $this->get_members_save_data($prime_id,$sum_insured,$over_all_total_premium,$gst);
echo json_encode(array('success' => true, 'message' => "Renewal Members successfully added", 'renewal_mem_cover_list' => $renewal_mem_cover_list));
}else{
$renewal_call_mem_upd_qry = 'UPDATE cw_health_renewals_insured_detail_log SET prime_health_id = "'.$prime_health_id.'",prime_health_renewals_id = "'.$prime_id.'",insured_name = "'.$insured_name.'",relationship = "'.$relationship.'",insured_dob = "'.$insured_dob.'",insured_gender = "'.$insured_gender.'",ped = "'.$ped.'",mem_sum_insured = "'.$mem_sum_insured.'",tpa_card_no = "'.$tpa_card_no.'",premium = "'.$premium.'",plus_on_premium = "'.$plus_on_premium.'",net_premium = "'.$net_premium.'",gst = "'.$gst.'",total_premium = "'.$total_premium.'",pre_disease_cover = "'.$pre_disease_cover.'",ped_remarks = "'.$ped_remarks.'",disease_note = "'.$disease_note.'",trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'" where prime_health_renewals_insured_log_id = "'.$prime_health_renewals_mem_log_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$renewal_call_mem_upd_qry')");
$renewal_call_mem_amt_upd_qry = 'UPDATE cw_health_renewals_insured_detail_log SET mem_sum_insured = "'.$mem_sum_insured.'",premium = "'.$premium.'",plus_on_premium = "'.$plus_on_premium.'",net_premium = "'.$net_premium.'",gst = "'.$gst.'",total_premium = "'.$total_premium.'",trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'" where prime_health_renewals_id = "'.$prime_health_renewals_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$renewal_call_mem_amt_upd_qry')");
//HEALTH RENEWALS TABLE UPD QUERY
$renewals_qry = 'UPDATE cw_health_renewals SET sum_insured = "'.$mem_sum_insured.'",premium = "'.$premium.'",plus_on_premium = "'.$plus_on_premium.'",net_premium = "'.$net_premium.'",gst = "'.$gst.'",total_premium = "'.$total_premium.'",trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'" where prime_health_renewals_id = "'.$prime_health_renewals_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$renewals_qry')");
// $renewals_log_qry = 'UPDATE cw_health_renewals_log SET renewal_sum_insured = "'.$mem_sum_insured.'",renewal_premium_amount = "'.$net_premium.'",renewal_total_premium = "'.$total_premium.'",trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'" where prime_health_renewals_id = "'.$prime_health_renewals_id.'" order by prime_health_renewals_id DESC LIMIT 1';
// $this->db->query("CALL sp_a_run ('RUN','$renewals_log_qry')");
$members_covered = $this->members_covered_sum($prime_id);
// die;
// $policy_type = (int)$members_covered['policy_type'];
$sum_insured = (int)$members_covered['sum_insured'];
$over_all_total_premium = $members_covered['total_premium'];
$gst = $members_covered['gst'];
$renewal_mem_cover_list = $this->get_members_save_data($prime_id,$sum_insured,$over_all_total_premium,$gst);
echo json_encode(array('success' => true, 'message' => "Renewals Members successfully updated", 'renewal_mem_cover_list' => $renewal_mem_cover_list));
}
}
//MEMBERS COVERED SUM
public function members_covered_sum($prime_id){
$health_renewals_query = "SELECT cw_sum_insured.sum_insured,total_premium,gst FROM cw_health_renewals inner join cw_sum_insured on cw_sum_insured.prime_sum_insured_id = cw_health_renewals.sum_insured where cw_health_renewals.prime_health_renewals_id = '$prime_id' and cw_health_renewals.trans_status = '1'";
$health_renewals_info = $this->db->query('CALL sp_a_run ("SELECT","'.$health_renewals_query.'")');
$health_renewals_result = $health_renewals_info->result();
$health_renewals_info->next_result();
$sum_insured = (int)$health_renewals_result[0]->sum_insured;
$total_premium = (int)$health_renewals_result[0]->total_premium;
$gst = (int)$health_renewals_result[0]->gst;
return array('sum_insured' => $sum_insured, 'total_premium' => $total_premium, 'gst' => $gst);
}
public function get_members_save_data($prime_id,$sum_insured,$over_all_total_premium,$gst){
$select_renewal_mem_qry = 'select *,cw_sum_insured.sum_insured,cw_relationship.relationship,gender,cw_status_mode.status_name as ped_mode from cw_health_renewals_insured_detail_log inner join cw_sum_insured on cw_sum_insured.prime_sum_insured_id = cw_health_renewals_insured_detail_log.mem_sum_insured inner join cw_relationship on cw_relationship.prime_relationship_id = cw_health_renewals_insured_detail_log.relationship inner join cw_gender on cw_gender.prime_gender_id = cw_health_renewals_insured_detail_log.insured_gender inner join cw_status_mode on cw_status_mode.prime_status_mode_id = cw_health_renewals_insured_detail_log.ped where prime_health_renewals_id = "'.$prime_id.'" and cw_health_renewals_insured_detail_log.trans_status = 1';
// select *,cw_relationship.relationship,gender,sum_insured,cw_status_mode.status_name as ped_mode,status.status_name as disease_cover from cw_health_renewals_insured_detail_log inner join cw_relationship on cw_relationship.prime_relationship_id = cw_health_renewals_insured_detail_log.relationship inner join cw_sum_insured on cw_sum_insured.prime_sum_insured_id = cw_health_renewals_insured_detail_log.mem_sum_insured inner join cw_gender on cw_gender.prime_gender_id = cw_health_renewals_insured_detail_log.insured_gender inner join cw_status_mode on cw_status_mode.prime_status_mode_id = cw_health_renewals_insured_detail_log.ped inner join cw_status_mode as status on status.prime_status_mode_id = cw_health_renewals_insured_detail_log.pre_disease_cover where prime_health_renewals_id = "'.$prime_id.'" and cw_health_renewals_insured_detail_log.trans_status = 1';
$select_renewal_mem_info = $this->db->query("CALL sp_a_run ('SELECT','$select_renewal_mem_qry')");
$select_renewal_mem_result = $select_renewal_mem_info->result();
$select_renewal_mem_info->next_result();
$count = 0;
foreach($select_renewal_mem_result as $rslt){
$count++;
$prime_health_renewals_insured_log_id = $rslt->prime_health_renewals_insured_log_id;
// $renewals_type = $rslt->renewal_details;
$insured_name = $rslt->insured_name;
$relationship = $rslt->relationship;
$gender = $rslt->gender;
$insured_dob = date('d-m-Y',strtotime($rslt->insured_dob));
$tpa_card_no = $rslt->tpa_card_no;
$mem_sum_insured = $rslt->sum_insured;
$premium = $rslt->premium;
$net_premium = $rslt->net_premium;
$gst = $rslt->gst;
$total_premium = $rslt->total_premium;
$edit_btn = "<a class='btn btn-edit btn-xs row_btn' onclick = health_mem_log_edit('$prime_health_renewals_insured_log_id');>Edit</a>";
$remove_btn = "<a class='btn btn-danger btn-xs row_btn' onclick = health_mem_log_remove('$prime_health_renewals_insured_log_id');>Delete</a>";
$td_btn = "";
// if($rslt_count === $count || $rslt_count === 1){
// $td_btn = "$edit_btn $remove_btn";
// }
// if((int)$count){
// $td_btn = "$edit_btn $remove_btn";
// }
// <td>$count</td>
$tr_line .= "<tr>
<td>$insured_name</td>
<td>$relationship</td>
<td>$insured_dob</td>
<td>$gender</td>
<td>$tpa_card_no</td>
<td style='white-space:nowrap;'>$edit_btn $remove_btn</td>
</tr>";
}
// $end_date = new DateTime();
// $end_date = $end_date->format("Y-m-d");
// $fetch_qry = 'SELECT start_date,end_date,gst,additional_gst,rider_gst FROM cw_product_type where cw_product_type.trans_status = 1 and cw_product_type.product_type_category = 2 and end_date >= date("'.$end_date.'")';
// $fetch_info = $this->db->query("CALL sp_a_run ('SELECT','$fetch_qry')");
// $fetch_result = $fetch_info->result();
// $fetch_info->next_result();
// if($fetch_result){
// $gst = $fetch_result[0]->gst;
// }
$tax_per = (floatval($gst) / 100) + 1;
$net_premium = (int)round($over_all_total_premium /floatval($tax_per),2);
$gst_amount = round($over_all_total_premium - $net_premium);
// $total_tr = "";
// $col_span = (int)$count - 1;
$total_tr = "<tr>
<td colspan='4' style='text-align:right;font-weight:bold;color:#00b0eb;'>Sum Insured</td>
<td colspan='2' style='font-weight:bold;'>$mem_sum_insured</td>
</tr>
<tr>
<td colspan='4' style='text-align:right;font-weight:bold;color:#00b0eb;'>Net Premium</td>
<td colspan='2' style='font-weight:bold;'>$net_premium</td>
</tr>
<tr>
<td colspan='4' style='text-align:right;font-weight:bold;color:#00b0eb;'>GST</td>
<td colspan='2' style='font-weight:bold;'>$gst_amount</td>
</tr>
<tr>
<td colspan='4' style='text-align:right;font-weight:bold;color:#00b0eb;'>Total Premium</td>
<td colspan='2' style='font-weight:bold;'>$over_all_total_premium</td>
</tr>";
// <tr>
// <td colspan='3' style='text-align:right;font-weight:bold;color:#00b0eb;'>Sum Assured</td>
// <td colspan='2' style='font-weight:bold;'>$sum_insured</td>
// </tr>
$renewal_mem_cover_list = "<table id='renewal_mem_cover_list' class='table table-bordered' style='background-color: #FFFFFF; box-shadow: none;'>
<thead>
<tr>
<th>Insured Name</th>
<th>Relationship</th>
<th>Insured DOB</th>
<th>Gender</th>
<th>Tpa Card No</th>
<th>Option</th>
</tr>
</thead>
<tbody>
$tr_line
$total_tr
</tbody>
</table>";
// <th>Sum Insured</th>
// <th>Total Premium</th>
return $renewal_mem_cover_list;
}
//COMPANY LIST
// public function get_company_list(){
// $company_query = "SELECT DISTINCT(prime_company_id),company_name FROM cw_product INNER join cw_company on prime_company_id = product_company where cw_product.trans_status = '1' and product_category = '2'";
// $company_info = $this->db->query('CALL sp_a_run ("SELECT","'.$company_query.'")');
// $company_result = $company_info->result();
// $company_info->next_result();
// echo json_encode(array('success' => TRUE, 'company_result' => $company_result));
// }
// //PRODUCT LIST
// public function get_product_list(){
// $company = $this->input->post('company');
// $product_query = "SELECT prime_product_id,product_name FROM `cw_product` where product_category = '2' and product_company = '$company' and trans_status = '1'";
// $product_info = $this->db->query('CALL sp_a_run ("SELECT","'.$product_query.'")');
// $product_result = $product_info->result();
// $product_info->next_result();
// echo json_encode(array('success' => TRUE, 'product_result' => $product_result));
// }
//MEMBERS INFORMATION
public function get_cust_info(){
$cust_id = $this->input->post('cust_id');
$prime_id_val = $this->input->post('prime_id_val');
$fetch_qry = 'SELECT * FROM cw_customer inner join cw_customer_cf on cw_customer.prime_customer_id = cw_customer_cf.prime_customer_id where cw_customer.prime_customer_id = "'.$cust_id.'"';
$fetch_info = $this->db->query("CALL sp_a_run ('SELECT','$fetch_qry')");
$fetch_result = $fetch_info->result();
$fetch_info->next_result();
$dob = $fetch_result[0]->dob;
$policy_query = str_replace("@SELECT@","*",$this->base_query);
$policy_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.$this->prime_id = '$prime_id_val'";
$policy_info = $this->db->query('CALL sp_a_run ("SELECT","'.$policy_query.'")');
$policy_result = $policy_info->result();
$policy_info->next_result();
$inception_date = $policy_result[0]->inception_date;
$current_date = new DateTime();
$current_date = $current_date->format("Y-m-d");
$dob_date = new DateTime($dob);
$dob_date = $dob_date->format("Y-m-d");
$date_parts1 = explode("-", $current_date);
$date_parts2 = explode("-", $dob_date);
$start_date = gregoriantojd($date_parts1[1], $date_parts1[2], $date_parts1[0]);
$end_date = gregoriantojd($date_parts2[1], $date_parts2[2], $date_parts2[0]);
$diff = abs($end_date - $start_date);
if((int)$diff === 365){
$age = 1;
}else{
$age = floor($diff / 365.25);
}
$dob_date = new DateTime();
$dob_date = $dob_date->format("Y-m-d");
$inception_date = new DateTime($inception_date);
$inception_date = $inception_date->format("Y-m-d");
$date_parts1 = explode("-", $dob_date);
$date_parts2 = explode("-", $inception_date);
$start_date = gregoriantojd($date_parts1[1], $date_parts1[2], $date_parts1[0]);
$end_date = gregoriantojd($date_parts2[1], $date_parts2[2], $date_parts2[0]);
$diff = abs($end_date - $start_date);
if((int)$diff === 365){
$inception_age = 1;
}else{
$inception_age = floor($diff / 365.25);
}
$age_at_start = (int)$age - (int)$inception_age;
$date_of_birth = new DateTime($dob);
$date_of_birth = $date_of_birth->format("d-m-Y");
echo json_encode(array('success' => true, 'date_of_birth' => $date_of_birth, 'age_at_start' => $age_at_start));
}
//EDIT HEALTH MEMBERS COVER LOG
public function health_mem_log_edit(){
$prime_health_mem_log_id = (int)$this->input->post('prime_health_mem_log_id');
$prime_health_mem_log_qry = 'select * from cw_health_renewals_insured_detail_log where cw_health_renewals_insured_detail_log.prime_health_renewals_insured_log_id = "'.$prime_health_mem_log_id.'" and trans_status = 1';
$prime_health_mem_log_info = $this->db->query("CALL sp_a_run ('SELECT','$prime_health_mem_log_qry')");
$prime_health_mem_log_result = $prime_health_mem_log_info->result();
$prime_health_mem_log_info->next_result();
echo json_encode(array('success' => TRUE, 'prime_health_mem_log_result' => $prime_health_mem_log_result[0]));
}
//REMOVE HEALTH MEMBERS COVER LOG
public function health_mem_log_remove(){
$prime_health_mem_log_id = (int)$this->input->post('prime_health_mem_log_id');
$prime_health_renewals_id = (int)$this->input->post('prime_health_renewals_id');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
$final_delete_qry = 'UPDATE cw_health_renewals_insured_detail_log SET trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" , trans_status = 0 WHERE cw_health_renewals_insured_detail_log.prime_health_renewals_insured_log_id = "'.$prime_health_mem_log_id.'"';
$this->db->query("CALL sp_a_run ('SELECT','$final_delete_qry')");
$members_covered = $this->members_covered_sum($prime_health_renewals_id);
// die;
// $policy_type = (int)$members_covered['policy_type'];
$sum_insured = (int)$members_covered['sum_insured'];
$over_all_total_premium = $members_covered['total_premium'];
$gst = $members_covered['gst'];
$renewal_mem_cover_list = $this->get_members_save_data($prime_health_renewals_id,$sum_insured,$over_all_total_premium,$gst);
echo json_encode(array('success' => TRUE, 'message' => "Remove Successfully",'renewal_mem_cover_list' => $renewal_mem_cover_list));
}
//CANCEL UPDATED UI
public function health_mem_log_cancel(){
$prime_health_renewals_id = (int)$this->input->post('prime_health_renewals_id');
$members_covered = $this->members_covered_sum($prime_health_renewals_id);
// die;
// $policy_type = (int)$members_covered['policy_type'];
$sum_insured = (int)$members_covered['sum_insured'];
$over_all_total_premium = $members_covered['total_premium'];
$gst = $members_covered['gst'];
$renewal_mem_cover_list = $this->get_members_save_data($prime_health_renewals_id,$sum_insured,$over_all_total_premium,$gst);
echo json_encode(array('success' => TRUE, 'message' => "Cancelled Successfully",'renewal_mem_cover_list' => $renewal_mem_cover_list));
}
//FETCH RENEWAL INFORMATION
public function renewal($policy_id){
$data['policy_id'] = $policy_id;
$health_renewals_qry = "select *,cw_health_company.company_name,cw_health_product.product_name,cw_payment_mode.payment_mode from $this->prime_table inner join cw_health_company on cw_health_company.prime_health_company_id = $this->prime_table.company inner join cw_health_product on cw_health_product.prime_health_product_id = $this->prime_table.product inner join cw_payment_mode on cw_payment_mode.prime_payment_mode_id = $this->prime_table.payment_mode where $this->prime_table.$this->prime_id = $policy_id and $this->prime_table.trans_status = 1";
$health_renewals_data = $this->db->query("CALL sp_a_run ('SELECT','$health_renewals_qry')");
$health_renewals_rslt = $health_renewals_data->result();
$health_renewals_data->next_result();
$data['policy_result'] = $health_renewals_rslt[0];
$sum_insured_qry = "SELECT * FROM cw_sum_insured where trans_status = '1'";
$sum_insured_info = $this->db->query('CALL sp_a_run ("SELECT","'.$sum_insured_qry.'")');
$sum_insured_result = $sum_insured_info->result();
$sum_insured_info->next_result();
$renewal_sum_insured[""] = "---- Sum Insured ----";
foreach($sum_insured_result as $rslt){
$renewal_sum_insured[$rslt->prime_sum_insured_id] = $rslt->sum_insured;
}
$data['renewal_sum_insured_list'] = $renewal_sum_insured;
$relationship_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_relationship` where trans_status = 1')");
$relationship_result = $relationship_info->result();
$relationship_info->next_result();
$relationship_list["0"] = "---- Relationship Type ----";
foreach($relationship_result as $for){
$prime_relationship_id = $for->prime_relationship_id;
$relationship = $for->relationship;
$relationship_list[$prime_relationship_id] = $relationship;
}
$data['relationship_list'] = $relationship_list;
$gender_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_gender` where trans_status = 1')");
$gender_result = $gender_info->result();
$gender_info->next_result();
$gender_list["0"] = "---- Select Gender ----";
foreach($gender_result as $for){
$prime_gender_id = $for->prime_gender_id;
$gender = $for->gender;
$gender_list[$prime_gender_id] = $gender;
}
$data['gender_list'] = $gender_list;
$status_mode_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_status_mode` where trans_status = 1')");
$status_mode_result = $status_mode_info->result();
$status_mode_info->next_result();
$status_mode_list["0"] = "---- Select Mode ----";
foreach($status_mode_result as $for){
$prime_status_mode_id = $for->prime_status_mode_id;
$status_name = $for->status_name;
$status_mode_list[$prime_status_mode_id] = $status_name;
}
$data['status_mode_list'] = $status_mode_list;
$sum_insured_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_sum_insured` where trans_status = 1')");
$sum_insured_result = $sum_insured_info->result();
$sum_insured_info->next_result();
$sum_insured_list["0"] = "---- Sum Insured ----";
foreach($sum_insured_result as $for){
$prime_sum_insured_id = $for->prime_sum_insured_id;
$sum_insured = $for->sum_insured;
$sum_insured_list[$prime_sum_insured_id] = $sum_insured;
}
$data['sum_insured_list'] = $sum_insured_list;
//RENEWALS CALL FUNCTION
$data['next_renewal'] = $this->next_renewal_data($policy_id);
$data['renewal_result'] = $this->renewal_data($policy_id);
$members_covered = $this->members_covered_sum($policy_id);
$sum_insured = (int)$members_covered['sum_insured'];
$over_all_total_premium = $members_covered['total_premium'];
$gst = $members_covered['gst'];
$data['renewal_mem_cover_list'] = $this->get_members_save_data($policy_id,$sum_insured,$over_all_total_premium,$gst);
$this->load->view("$this->control_name/renewal",$data);
}
//SAVE NEXT RENEWAL DATA
public function renewal_update(){
$prime_renewal_id = (int)$this->input->post('prime_renewal_id');
$prime_health_renewals_id = (int)$this->input->post('prime_health_renewals_id');
$renewal_from_date = date('Y-m-d',strtotime($this->input->post('renewal_from_date')));
$renewal_to_date = date('Y-m-d',strtotime($this->input->post('renewal_to_date')));
$renewal_due_mon = date("F", strtotime("$renewal_to_date"));
$renewal_due_year = date("Y", strtotime("$renewal_to_date"));
$renewed_date = date('Y-m-d',strtotime($this->input->post('renewed_date')));
$renewed_mon = date("F", strtotime("$renewed_date"));
$renewed_year = date("Y", strtotime("$renewed_date"));
$renewal_mode = $this->input->post('renewal_mode');
$renewal_covered_year = $this->input->post('renewal_covered_year');
$renewal_policy_no = $this->input->post('renewal_policy_no');
$renewal_premium_amount = $this->input->post('renewal_premium_amount');
$renewal_plus_on_premium = $this->input->post('renewal_plus_on_premium');
$renewal_gst = $this->input->post('renewal_gst');
$renewal_net_premium = $this->input->post('renewal_net_premium');
$renewal_total_premium = $this->input->post('renewal_total_premium');
$renewal_sum_insured = $this->input->post('renewal_sum_insured');
$renewal_ncb = $this->input->post('renewal_ncb');
$renewal_ncb_amount = $this->input->post('renewal_ncb_amount');
$renewal_note = $this->input->post('renewal_note');
$logged_id = $this->session->userdata('logged_id');
$created_on = date("Y-m-d H:i:s");
if($prime_renewal_id === 0){
$exist_count = 0;
if((int)$renewal_mode === 1 || (int)$renewal_mode === 4 || (int)$renewal_mode === 6){
$is_exist_qry = 'SELECT count(prime_renewal_id) as rslt_count 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 where cw_health_renewals_log.prime_health_renewals_id = "'.$prime_health_renewals_id.'" and cw_health_renewals_log.renewal_policy_no = "'.$renewal_policy_no.'" and cw_health_renewals_log.renewal_mode in (1,4,6) and cw_health_renewals.company != 2 and cw_health_renewals_log.trans_status = 1';
// $is_exist_qry = 'SELECT SUM(CASE WHEN renewal_mode = 2 THEN 1 ELSE 0 END) as half_mode_count,SUM(CASE WHEN renewal_mode = 3 THEN 1 ELSE 0 END) as month_mode_count,SUM(CASE WHEN renewal_mode = 5 THEN 1 ELSE 0 END) as quater_mode_count FROM cw_health_renewals_log where prime_health_renewals_id = "'.$prime_health_renewals_id.'" and renewal_policy_no = "'.$renewal_policy_no.'" and trans_status = 1';
$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
$exist_rslt = $is_exist_data->result();
$is_exist_data->next_result();
$exist_count = (int)$exist_rslt[0]->rslt_count;
}
if($exist_count === 0){
$renewal_qry = 'INSERT INTO cw_health_renewals_log (prime_health_renewals_id,renewal_mode,renewal_from_date,renewal_to_date,renewed_date,renewal_covered_year,renewal_policy_no,renewal_premium_amount,renewal_plus_on_premium,renewal_gst,renewal_net_premium,renewal_total_premium,renewal_sum_insured,renewal_ncb,renewal_ncb_amount,renewal_note,from_health,trans_created_by, trans_created_date) VALUES ("'.$prime_health_renewals_id.'","'.$renewal_mode.'","'.$renewal_from_date.'","'.$renewal_to_date.'","'.$renewed_date.'","'.$renewal_covered_year.'","'.$renewal_policy_no.'","'.$renewal_premium_amount.'","'.$renewal_plus_on_premium.'","'.$renewal_gst.'","'.$renewal_net_premium.'","'.$renewal_total_premium.'","'.$renewal_sum_insured.'","'.$renewal_ncb.'","'.$renewal_ncb_amount.'","'.$renewal_note.'","0","'.$logged_id.'","'.$created_on.'")';
$this->db->query("CALL sp_a_run ('RUN','$renewal_qry')");
$is_exist_qry = 'SELECT IFNULL(sum(renewal_covered_year),0) as renewal_covered_year FROM cw_health_renewals_log where prime_health_renewals_id = "'.$prime_health_renewals_id.'" and trans_status = 1 ';
$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
$exist_rslt = $is_exist_data->result();
$is_exist_data->next_result();
$tot_covered_year = $exist_rslt[0]->renewal_covered_year;
// $update_query = 'UPDATE cw_health_renewals SET WHERE prime_health_renewals_id = "'.$prime_health_renewals_id.'"';
// $this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
// //CALL LOG COUNT UPDATE QUERY TO HEALTH RENEWAL TABLE
// $upd_call_count_qry = 'UPDATE cw_health_renewals SET current_policy_no = "'.$renewal_policy_no.'",covered_year = "'.$tot_covered_year.'",due_date = "'.$renewal_to_date.'",call_count = "0",renewal_status = "4",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE prime_health_renewals_id = "'.$prime_health_renewals_id.'" and trans_status = 1';
// $this->db->query("CALL sp_a_run ('SELECT','$upd_call_count_qry')");
//HEALTH RENEWALS TABLE UPD QUERY
$health_renewals_upd_qry = 'UPDATE cw_health_renewals SET sum_insured = "'.$renewal_sum_insured.'",premium = "'.$renewal_premium_amount.'",plus_on_premium = "'.$renewal_plus_on_premium.'",net_premium = "'.$renewal_net_premium.'",gst = "'.$renewal_gst.'",total_premium = "'.$renewal_total_premium.'",current_policy_no = "'.$renewal_policy_no.'",covered_year = "'.$tot_covered_year.'",payment_mode = "'.$renewal_mode.'",policy_from_date = "'.$renewal_from_date.'",policy_to_date = "'.$renewal_to_date.'",due_date = "'.$renewal_to_date.'",due_month = "'.$renewal_due_mon.'",due_year = "'.$renewal_due_year.'",call_count = "0",renewal_status = "4",renewed_date = "'.$renewed_date.'",renewed_month = "'.$renewed_mon.'",renewed_year = "'.$renewed_year.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" WHERE prime_health_renewals_id = "'.$prime_health_renewals_id.'" and trans_status = 1';
$this->db->query("CALL sp_a_run ('RUN','$health_renewals_upd_qry')");
$next_renewal = $this->next_renewal_data($prime_health_renewals_id);
$renewal_list = $this->renewal_data($prime_health_renewals_id);
echo json_encode(array('success' => true, 'message' => "Renewal successfully added", 'next_renewal' => $next_renewal, 'renewal_list' => $renewal_list));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Renewal already exist"));
}
}else{
$renewal_upd_qry = 'UPDATE cw_health_renewals_log SET renewal_mode = "'.$renewal_mode.'", renewal_from_date = "'.$renewal_from_date.'",renewal_to_date = "'.$renewal_to_date.'",renewed_date = "'.$renewed_date.'",renewal_covered_year = "'.$renewal_covered_year.'",renewal_policy_no = "'.$renewal_policy_no.'",renewal_premium_amount = "'.$renewal_premium_amount.'",renewal_plus_on_premium = "'.$renewal_plus_on_premium.'",renewal_gst = "'.$renewal_gst.'",renewal_net_premium = "'.$renewal_net_premium.'",renewal_total_premium = "'.$renewal_total_premium.'",renewal_sum_insured = "'.$renewal_sum_insured.'",renewal_ncb = "'.$renewal_ncb.'",renewal_ncb_amount = "'.$renewal_ncb_amount.'",renewal_note = "'.$renewal_note.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_renewal_id = "'.$prime_renewal_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$renewal_upd_qry')");
$is_exist_qry = 'SELECT IFNULL(sum(renewal_covered_year),0) as renewal_covered_year FROM cw_health_renewals_log where prime_health_renewals_id = "'.$prime_health_renewals_id.'" and trans_status = 1 ';
$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
$exist_rslt = $is_exist_data->result();
$is_exist_data->next_result();
$tot_covered_year = $exist_rslt[0]->renewal_covered_year;
//HEALTH RENEWALS TABLE UPD QUERY
$health_renewals_upd_qry = 'UPDATE cw_health_renewals SET sum_insured = "'.$renewal_sum_insured.'",premium = "'.$renewal_premium_amount.'",plus_on_premium = "'.$renewal_plus_on_premium.'",net_premium = "'.$renewal_net_premium.'",gst = "'.$renewal_gst.'",total_premium = "'.$renewal_total_premium.'",current_policy_no = "'.$renewal_policy_no.'",covered_year = "'.$tot_covered_year.'",payment_mode = "'.$renewal_mode.'",policy_from_date = "'.$renewal_from_date.'",policy_to_date = "'.$renewal_to_date.'",due_date = "'.$renewal_to_date.'",due_month = "'.$renewal_due_mon.'",due_year = "'.$renewal_due_year.'",call_count = "0",renewal_status = "4",renewed_date = "'.$renewed_date.'",renewed_month = "'.$renewed_mon.'",renewed_year = "'.$renewed_year.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" WHERE prime_health_renewals_id = "'.$prime_health_renewals_id.'" and trans_status = 1';
$this->db->query("CALL sp_a_run ('RUN','$health_renewals_upd_qry')");
$next_renewal = $this->next_renewal_data($prime_health_renewals_id);
$renewal_list = $this->renewal_data($prime_health_renewals_id);
echo json_encode(array('success' => true, 'message' => "Renewal successfully updated", 'next_renewal' => $next_renewal, 'renewal_list' => $renewal_list));
}
}
//GET NEXT RENEWAL DATA
public function next_renewal_data($prime_health_renewals_id){
$renewal_query = "select * from cw_health_renewals_log where prime_health_renewals_id = '$prime_health_renewals_id' and trans_status = '1' order by prime_renewal_id DESC LIMIT 0,1";
$renewal_info = $this->db->query('CALL sp_a_run ("SELECT","'.$renewal_query.'")');
$renewal_result = $renewal_info->result();
$renewal_info->next_result();
$next_renewal = $renewal_result[0];
// $renewal_mode_list = array("1"=>"+1 year","2"=>"+6 months","3"=>"+1 months","4"=>"+2 years","5"=>"+3 months","6"=>"+3 years","7"=>"Fully Paidup");
$renewal_mode_list = array("1"=>"+1 year","2"=>"+6 months","3"=>"+1 months","4"=>"+2 years","5"=>"+3 months","6"=>"+3 years");
// if((int)$next_renewal->renewal_mode !== 7){
$next_due = $renewal_mode_list[$next_renewal->renewal_mode];
if($next_renewal->renewal_to_date !== "0000-00-00"){
$policy_from = new DateTime($next_renewal->renewal_to_date);
$from_date = $policy_from->modify("+1 day");
$next_renewal->renewal_from_date = $from_date->format("d-m-Y");
$policy_to = $policy_from->modify("$next_due");
$policy_to = $policy_to->modify("-1 day");
$next_renewal->renewal_to_date = $policy_to->format("d-m-Y");
}
$next_renewal->renewal_policy_no = "";
$next_renewal->renewal_ncb = 2;
$next_renewal->renewal_ncb_amount = "";
$next_renewal->renewal_note = "";
$date_now = new DateTime();
$date_now = $date_now->modify("+2 months");
//$check_date = new DateTime($next_renewal->renewal_from_date);
$next_renewal->btn_sts = true;
// if($check_date > $date_now) {
// $next_renewal->btn_sts = false;
// }
$policy_from = new DateTime($next_renewal->renewal_from_date);
$policy_from = $policy_from->format("Y-m-d");
$policy_to = new DateTime($next_renewal->renewal_to_date);
$policy_to = $policy_to->modify("+1 day");
$policy_to = $policy_to->format("Y-m-d");
$covered_year = $this->covered_year_cal($next_renewal->renewal_mode,$policy_from,$policy_to);
// if($renewal_mode === "1"){
// $covered_year = "1 Year";
// }else
// if($renewal_mode === "2"){
// $covered_year = "6 Months";
// }else
// if($renewal_mode === "3"){
// $covered_year = "1 Month";
// }else
// if($renewal_mode === "4"){
// $covered_year = "2 Years";
// }else
// if($renewal_mode === "5"){
// $covered_year = "3 Months";
// }else
// if($renewal_mode === "6"){
// $covered_year = "3 Years";
// }
// }else{
// $next_renewal->renewal_from_date = date("d-m-Y",strtotime($next_renewal->renewal_from_date));
// $next_renewal->renewal_to_date = date("d-m-Y",strtotime($next_renewal->renewal_to_date));
// $covered_year = 0;
// }
$next_renewal->renewal_covered_year = $covered_year;
// echo "<pre>";
// print_r($next_renewal); die;
return $next_renewal;
}
//EDIT RENEWAL
public function renewal_edit(){
$prime_renewal_id = (int)$this->input->post('prime_renewal_id');
$renewal_query = "select * from cw_health_renewals_log where prime_renewal_id = '$prime_renewal_id' and trans_status = '1'";
$renewal_info = $this->db->query('CALL sp_a_run ("SELECT","'.$renewal_query.'")');
$renewal_result = $renewal_info->result();
$renewal_info->next_result();
echo json_encode(array('success' => TRUE, 'renewal_rslt' => $renewal_result[0]));
}
//REMOVE RENEWAL
public function renewal_remove(){
$prime_renewal_id = (int)$this->input->post('prime_renewal_id');
$prime_health_renewals_id = (int)$this->input->post('prime_health_renewals_id');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
$final_qry = 'UPDATE cw_health_renewals_log SET trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" , trans_status = 0 WHERE prime_renewal_id = "'.$prime_renewal_id.'"';
$this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$next_renewal = $this->next_renewal_data($prime_health_renewals_id);
$renewal_list = $this->renewal_data($prime_health_renewals_id);
echo json_encode(array('success' => TRUE, 'message' => "Remove Successfully",'renewal_list' => $renewal_list,'next_renewal' => $next_renewal));
}
//CANCEL UPDATED UI
public function renewal_cancel(){
$prime_health_renewals_id = (int)$this->input->post('prime_health_renewals_id');
$next_renewal = $this->next_renewal_data($prime_health_renewals_id);
$renewal_list = $this->renewal_data($prime_health_renewals_id);
echo json_encode(array('success' => TRUE, 'message' => "Cancelled Successfully",'renewal_list' => $renewal_list,'next_renewal' => $next_renewal));
}
// //GET NEXT DUE DATE
// public function update_to_date(){
// $policy_from = $this->input->post('renewal_from_date');
// $policy_from = new DateTime($policy_from);
// $policy_from = $policy_from->modify("+1 year");
// $policy_to = $policy_from->modify("-1 day");
// $renewal_to_date = $policy_to->format("d-m-Y");
// echo json_encode(array('success' => TRUE,'renewal_to_date' => $renewal_to_date));
// }
public function excel($module_id,$excel_format){
$excel_format_qry = 'select excel_line_column_name,excel_line_value from cw_util_excel_format_line where excel_line_module_id = "'.$module_id.'" and prime_excel_format_id ="'.$excel_format.'" and trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
require_once APPPATH."/third_party/PHPExcel.php";
$obj = new PHPExcel();
//Set the first row as the header row
foreach($excel_result as $excel){
$excel_line_column_name = $excel->excel_line_column_name;
$excel_line_value = $excel->excel_line_value;
$obj->getActiveSheet()->setCellValue($excel_line_value."1", $excel_line_column_name);
}
// Rename worksheet name
$filename= $module_id.".xls"; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = PHPExcel_IOFactory::createWriter($obj, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
echo json_encode(array('success' => TRUE, 'output' => $excelOutput));
}
//GET RENEWAL HISTORY
public function renewal_data($prime_health_renewals_id){
$policy_query = str_replace("@SELECT@","*",$this->base_query);
$policy_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.$this->prime_id = '$prime_health_renewals_id'";
$policy_info = $this->db->query('CALL sp_a_run ("SELECT","'.$policy_query.'")');
$policy_result = $policy_info->result();
$policy_info->next_result();
$policy_from = $policy_result[0]->policy_from;
//SELECT QUERY FOR GET A RENEWAL LOG DETAILS
$renewal_query = "select *,cw_sum_insured.sum_insured from cw_health_renewals_log inner join cw_sum_insured on cw_sum_insured.prime_sum_insured_id = cw_health_renewals_log.renewal_sum_insured where prime_health_renewals_id = '$prime_health_renewals_id' and cw_health_renewals_log.trans_status = '1'";
// order by prime_renewal_id DESC
$renewal_info = $this->db->query('CALL sp_a_run ("SELECT","'.$renewal_query.'")');
$renewal_result = $renewal_info->result();
$renewal_info->next_result();
$tr_line = "";
$count = 0;
$rslt_count = count($renewal_result);
// $renewal_mode_list = array("1"=>"1 year","2"=>"Semi Annual","3"=>"Monthly","4"=>"2 years","5"=>"Quaterly","6"=>"3 years","7"=>"Fully Paidup");
$renewal_mode_list = array("1"=>"1 year","2"=>"Semi Annual","3"=>"Monthly","4"=>"2 years","5"=>"Quaterly","6"=>"3 years");
$renewal_ncb_list = array('1'=>"Yes",'2'=>"No");
foreach($renewal_result as $rslt){
$count++;
$prime_renewal_id = $rslt->prime_renewal_id;
$prime_health_renewals_id = $rslt->prime_health_renewals_id;
if($rslt->renewal_from_date !== "0000-00-00"){
$renewal_from_date = date('d-m-Y',strtotime($rslt->renewal_from_date));
}
if($rslt->renewal_to_date !== "0000-00-00"){
$renewal_to_date = date('d-m-Y',strtotime($rslt->renewal_to_date));
}
$renewal_policy_no = $rslt->renewal_policy_no;
$renewal_premium_amount = $rslt->renewal_premium_amount;
$renewal_plus_on_premium = $rslt->renewal_plus_on_premium;
$renewal_gst = $rslt->renewal_gst;
$renewal_net_premium = $rslt->renewal_net_premium;
$renewal_total_premium = $rslt->renewal_total_premium;
$renewal_sum_insured = $rslt->sum_insured;
$renewal_ncb = $rslt->renewal_ncb;
$renewal_ncb_amount = $rslt->renewal_ncb_amount;
$renewal_covered_year = $rslt->renewal_covered_year;
$renewal_note = $rslt->renewal_note;
$renewal_mode = $rslt->renewal_mode;
$renewal_mode = $renewal_mode_list[$renewal_mode];
$renewal_ncb = $renewal_ncb_list[$renewal_ncb];
// $sum_insured_qry = "SELECT * FROM cw_sum_insured where prime_sum_insured_id = '$renewal_sum_insured' and trans_status = '1'";
// $sum_insured_info = $this->db->query('CALL sp_a_run ("SELECT","'.$sum_insured_qry.'")');
// $sum_insured_result = $sum_insured_info->result();
// $sum_insured_info->next_result();
// $renewal_sum_insured = $sum_insured_result[0]->sum_insured;
$paying_year = $this->paying_year($renewal_from_date,$renewal_to_date);
$edit_btn = "<a class='btn btn-edit btn-xs row_btn' onclick = renewal_edit('$prime_renewal_id');>Edit</a>";
$remove_btn = "<a class='btn btn-danger btn-xs row_btn' onclick = renewal_remove('$prime_renewal_id');>Delete</a>";
$td_btn = "";
if($rslt_count === $count || $rslt_count === 1){
$td_btn = "$edit_btn $remove_btn";
}
// if((int)$count === 1){
// $td_btn = "$edit_btn $remove_btn";
// }
$tr_line .= "<tr>
<td>$count</td>
<td>$renewal_mode</td>
<td style='white-space:nowrap;'>$renewal_from_date</td>
<td style='white-space:nowrap;'>$renewal_to_date</td>
<td>$renewal_covered_year</td>
<td>$renewal_policy_no</td>
<td>$renewal_sum_insured</td>
<td>$renewal_total_premium</td>
<td>$renewal_note</td>
<td style='white-space:nowrap;'>$td_btn</td>
</tr>";
// <td>$renewal_premium_amount</td>
// <td>$renewal_net_premium</td>
}
$renewal_list = "<table id='renewal_list' class='table table-bordered' style='background-color: #FFFFFF; box-shadow: none;'>
<thead>
<tr>
<th>S No</th>
<th>Mode</th>
<th>Policy From</th>
<th>Policy To</th>
<th>Covered</th>
<th>Policy No</th>
<th>Sum Insured</th>
<th>Total Premium</th>
<th>Renewal Note</th>
<th>Option</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
// <th>Premium</th>
// <th>Net Premium</th>
return $renewal_list;
}
// public static function paying_year($start_date, $end_date){
// $start_date = new DateTime($start_date);
// $end_date = new DateTime($end_date);
// $tot_month = $start_date->diff($end_date)->m + ($start_date->diff($end_date)->y*12);
// $paying_year = "";
// // if((int)$renewal_mode === 1){
// // $paying_year = floor($tot_month/12);
// // }else{
// $paying_year = floor($tot_month/12) +1;
// // }
// return $paying_year;
// }
public static function paying_year($start_date, $end_date){
$start_date = new DateTime($start_date);
$end_date = new DateTime($end_date);
$tot_month = $start_date->diff($end_date)->m + ($start_date->diff($end_date)->y*12);
$paying_year = floor($tot_month/12)+1;
return $paying_year;
}
public function policy_no_exist(){
$renewal_mode = (int)$this->input->post('mode');
$policy_no = trim($this->input->post('policy_no'));
if($renewal_mode === 1 || $renewal_mode === 4 || $renewal_mode === 6){
$exist_query = 'SELECT count(*) as policy_count FROM cw_health_renewals where policy_number = "'.$policy_no.'" and cw_health_renewals.payment_mode in (1,4,6) and cw_health_renewals.company != 2 and cw_health_renewals.trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_result = $exist_data->result();
$exist_data->next_result();
if((int)$exist_result[0]->policy_count > 0){
echo json_encode(array('success' => false, 'message' => "Policy number already exist"));
}else{
$exist_query = 'SELECT count(*) as policy_count 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 where cw_health_renewals_log.renewal_policy_no = "'.$policy_no.'" and cw_health_renewals_log.renewal_mode in (1,4,6) and cw_health_renewals.company != 2 and cw_health_renewals_log.trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_result = $exist_data->result();
$exist_data->next_result();
if((int)$exist_result[0]->policy_count > 0){
echo json_encode(array('success' => false, 'message' => "Policy number already exist in renewal"));
}else{
echo json_encode(array('success' => true, 'message' => "proceed"));
}
}
}else{
echo json_encode(array('success' => true, 'message' => "proceed"));
}
}
//HEALTH CALL LOG FUNCTION START
public function call_log($form_view_id=-1){
//VIEW, FORM INPUT
// $data['view_info'] = $this->view_info;
// $data['form_info'] = $this->form_info;
$data['health_renewals_id'] = $form_view_id;
//VIEW DATA
$base_query = str_replace("@SELECT@",$this->view_select,$this->base_query);
$view_query = $base_query ." where $this->prime_table.$this->prime_id = $form_view_id and $this->prime_table.trans_status = 1";
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_query')");
$view_result = $view_data->result();
$view_data->next_result();
$data['form_view_id'] = $form_view_id;
$data['form_view'] = $view_result[0];
$health_renewals_qry = "select *,cw_health_company.company_name,cw_health_product.product_name,cw_payment_mode.payment_mode from $this->prime_table inner join cw_health_company on cw_health_company.prime_health_company_id = $this->prime_table.company inner join cw_health_product on cw_health_product.prime_health_product_id = $this->prime_table.product inner join cw_payment_mode on cw_payment_mode.prime_payment_mode_id = $this->prime_table.payment_mode where $this->prime_table.$this->prime_id = $form_view_id and $this->prime_table.trans_status = 1";
// echo $health_renewals_qry;die;
$health_renewals_data = $this->db->query("CALL sp_a_run ('SELECT','$health_renewals_qry')");
$health_renewals_rslt = $health_renewals_data->result();
$health_renewals_data->next_result();
$data['health_renewals_rslt'] = $health_renewals_rslt[0];
$login_code = $health_renewals_rslt[0]->login_code;
$employee_code = $health_renewals_rslt[0]->employee_code;
$login_emp_query = 'select cw_employees.login_code,cw_employees.employee_name,cw_employees.date_of_joining,channel_name,designation_name,branch_name,concat(a.login_code," - ",a.employee_name) as level1,concat(b.login_code," - ",b.employee_name) as level2 from cw_employees inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel inner join cw_designation on cw_designation.prime_designation_id = cw_employees.designation inner join cw_employees a on cw_employees.level_1_reporting_person_code = a.employee_code inner join cw_employees b on cw_employees.level_2_reporting_person_code = b.employee_code where cw_employees.employee_code != "" and cw_employees.employee_code = "'.$employee_code.'"';
// echo $login_emp_query;die;
$login_emp_info = $this->db->query("CALL sp_a_run ('SELECT','$login_emp_query')");
$login_emp_rlst = $login_emp_info->result();
$login_emp_info->next_result();
$data['login_emp_rslt'] = $login_emp_rlst[0];
$call_status_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_health_call_status` where trans_status = 1')");
$call_status_result = $call_status_info->result();
$call_status_info->next_result();
$call_status_list["0"] = "---- Call Status Type ----";
foreach($call_status_result as $for){
$call_status_id = $for->prime_health_call_status_id;
$health_call_status = $for->health_call_status;
$call_status_list[$call_status_id] = $health_call_status;
}
$data['call_status_list'] = $call_status_list;
// $health_interested_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_health_interested` where trans_status = 1')");
// $health_interested_result = $health_interested_info->result();
// $health_interested_info->next_result();
// $health_interested_list["0"] = "---- Health Interested ----";
// foreach($health_interested_result as $for){
// $health_interested_id = $for->prime_health_interested_id;
// $health_interested = $for->health_interested;
// $health_interested_list[$health_interested_id] = $health_interested;
// }
// $data['health_interested_list'] = $health_interested_list;
// $health_not_interested_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_health_not_interested` where trans_status = 1')");
// $health_not_interested_result = $health_not_interested_info->result();
// $health_not_interested_info->next_result();
// $health_not_interested_list["0"] = "---- Health Not Interested ----";
// foreach($health_not_interested_result as $for){
// $health_not_interested_id = $for->prime_health_not_interested_id;
// $health_not_interested = $for->health_not_interested;
// $health_not_interested_list[$health_not_interested_id] = $health_not_interested;
// }
// $data['health_not_interested_list'] = $health_not_interested_list;
$health_not_contactable_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_health_not_contactable` where trans_status = 1')");
$health_not_contactable_result = $health_not_contactable_info->result();
$health_not_contactable_info->next_result();
$health_not_contactable_list["0"] = "---- Health Not Contactable ----";
foreach($health_not_contactable_result as $for){
$health_not_contactable_id = $for->prime_health_not_contactable_id;
$health_not_contactable = $for->health_not_contactable;
$health_not_contactable_list[$health_not_contactable_id] = $health_not_contactable;
}
$data['health_not_contactable_list'] = $health_not_contactable_list;
$health_other_ways_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_health_other_ways` where trans_status = 1')");
$health_other_ways_result = $health_other_ways_info->result();
$health_other_ways_info->next_result();
$health_other_ways_list["0"] = "---- Health Other Ways ----";
foreach($health_other_ways_result as $for){
$health_other_ways_id = $for->prime_health_other_ways_id;
$health_other_ways = $for->health_other_ways;
$health_other_ways_list[$health_other_ways_id] = $health_other_ways;
}
$data['health_other_ways_list'] = $health_other_ways_list;
$health_whatsapp_sent_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_health_whatsapp_sent` where trans_status = 1')");
$health_whatsapp_sent_result = $health_whatsapp_sent_info->result();
$health_whatsapp_sent_info->next_result();
$health_whatsapp_sent_list["0"] = "---- Health Whatsapp Sent ----";
foreach($health_whatsapp_sent_result as $for){
$health_whatsapp_sent_id = $for->prime_health_whatsapp_sent_id;
$health_whatsapp_sent = $for->health_whatsapp_sent;
$health_whatsapp_sent_list[$health_whatsapp_sent_id] = $health_whatsapp_sent;
}
$data['health_whatsapp_sent_list'] = $health_whatsapp_sent_list;
$health_mail_sent_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_health_mail_sent` where trans_status = 1')");
$health_mail_sent_result = $health_mail_sent_info->result();
$health_mail_sent_info->next_result();
$health_mail_sent_list["0"] = "---- Health Mail Sent ----";
foreach($health_mail_sent_result as $for){
$health_mail_sent_id = $for->prime_health_mail_sent_id;
$health_mail_sent = $for->health_mail_sent;
$health_mail_sent_list[$health_mail_sent_id] = $health_mail_sent;
}
$data['health_mail_sent_list'] = $health_mail_sent_list;
$health_tried_thr_sales_team_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_health_tried_thr_sales_team` where trans_status = 1')");
$health_tried_thr_sales_team_result = $health_tried_thr_sales_team_info->result();
$health_tried_thr_sales_team_info->next_result();
$health_tried_thr_list["0"] = "---- Health Tried Thr ----";
foreach($health_tried_thr_sales_team_result as $for){
$health_tried_id = $for->prime_health_tried_thr_sales_team_id;
$health_tried_team = $for->health_tried_thr_sales_team;
$health_tried_thr_list[$health_tried_id] = $health_tried_team;
}
$data['health_tried_thr_list'] = $health_tried_thr_list;
$payment_select_mode_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_payment_select_mode` where trans_status = 1')");
$payment_select_mode_rslt = $payment_select_mode_info->result();
$payment_select_mode_info->next_result();
$payment_mode_list["0"] = "---- Payment Mode ----";
foreach($payment_select_mode_rslt as $for){
$payment_id = $for->prime_payment_select_mode_id;
$payment_mode = $for->payment_mode;
$payment_mode_list[$payment_id] = $payment_mode;
}
$data['payment_mode_list'] = $payment_mode_list;
$home_visit_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_health_home_visit` where trans_status = 1')");
$home_visit_result = $home_visit_info->result();
$home_visit_info->next_result();
$home_visit_list["0"] = "---- Health Home Visit ----";
foreach($home_visit_result as $for){
$home_visit_id = $for->prime_health_home_visit_id;
$home_visit = $for->health_home_visit;
$home_visit_list[$home_visit_id] = $home_visit;
}
$data['home_visit_list'] = $home_visit_list;
$health_noc_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_health_noc` where trans_status = 1')");
$health_noc_result = $health_noc_info->result();
$health_noc_info->next_result();
$health_noc_list["0"] = "---- Health NOC ----";
foreach($health_noc_result as $for){
$health_noc_id = $for->prime_health_noc_id;
$health_noc = $for->health_noc;
$health_noc_list[$health_noc_id] = $health_noc;
}
$data['health_noc_list'] = $health_noc_list;
$health_in_progress_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_health_in_progress` where trans_status = 1')");
$health_in_progress_result = $health_in_progress_info->result();
$health_in_progress_info->next_result();
$health_in_progress_list["0"] = "---- Health In Progress ----";
foreach($health_in_progress_result as $for){
$health_in_progress_id = $for->prime_health_in_progress_id;
$health_in_progress = $for->health_in_progress;
$health_in_progress_list[$health_in_progress_id] = $health_in_progress;
}
$data['in_progress_list'] = $health_in_progress_list;
// $health_not_attended_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_life_na` where trans_status = 1')");
// $health_not_attended_result = $health_not_attended_info->result();
// $health_not_attended_info->next_result();
// $health_not_attended_list["0"] = "---- Health Not Attended ----";
// foreach($health_not_attended_result as $for){
// $health_not_attended_id = $for->prime_life_na_id;
// $health_not_attended = $for->life_na;
// $health_not_attended_list[$health_not_attended_id] = $health_not_attended;
// }
// $data['health_not_attended_list'] = $health_not_attended_list;
$health_group_policy_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_health_group_policy` where trans_status = 1')");
$health_group_policy_result = $health_group_policy_info->result();
$health_group_policy_info->next_result();
$health_group_policy_list["0"] = "---- Health Group Policy ----";
foreach($health_group_policy_result as $for){
$health_group_policy_id = $for->prime_health_group_policy_id;
$health_group_policy = $for->group_policy;
$health_group_policy_list[$health_group_policy_id] = $health_group_policy;
}
$data['health_group_policy_list'] = $health_group_policy_list;
$emp_code_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT employee_code,employee_name FROM `cw_employees` where trans_status = 1 and employee_status = 1')");
$emp_code_result = $emp_code_info->result();
$emp_code_info->next_result();
$emp_code_list["0"] = "---- Select Employee ----";
foreach($emp_code_result as $for){
$employee_code = $for->employee_code;
$employee_name = $for->employee_name;
$emp_code_list[$employee_code] = $employee_code." - ".$employee_name;
}
$data['emp_code_list'] = $emp_code_list;
$data['renewal_call_log_list'] = $this->renewal_call_log_data($form_view_id);
$this->load->view("$this->control_name/log",$data);
}
public function sub_disposition_list(){
$call_status = (int)$this->input->post('call_status');
$sub_disp_qry = 'SELECT * from cw_sub_disposition where health_call_status = "'.$call_status.'"';
$sub_disp_info = $this->db->query("CALL sp_a_run ('SELECT','$sub_disp_qry')");
$sub_disp_result = $sub_disp_info->result();
$sub_disp_info->next_result();
echo json_encode(array('sub_disposition_list' => $sub_disp_result));
}
//CALL LOG ADD AND UPDATE FUNCTION
public function health_call_log_update(){
$prime_health_call_log_id = (int)$this->input->post('prime_health_renewals_call_log_id');
$prime_health_renewals_id = (int)$this->input->post('prime_health_renewals_id');
$sub_disposition = $this->input->post('sub_disposition');
$call_status = $this->input->post('call_status');
$ncd = date('Y-m-d G:i:s',strtotime($this->input->post('ncd')));
$remarks = $this->input->post('remarks');
$payment_mode = $this->input->post('payment_mode');
$logged_id = $this->session->userdata('logged_id');
$created_on = date("Y-m-d H:i:s");
//FOR UPDATE A RENEWAL STATUS TO RENEWALS TABLE
$renewal_status = "4";
if((int)$call_status === 8){
$renewal_status = "3";
}
if((int)$call_status === 2){
$renewal_status = "1";
}else
if((int)$call_status === 6){
$renewal_status = "5";
}
if($prime_health_call_log_id === 0){
$renewal_call_log_qry = 'INSERT INTO cw_health_renewals_call_log (prime_health_renewals_id,call_status,sub_disposition,ncd,remarks,payment_mode,lapsed_status,trans_created_by, trans_created_date) VALUES ("'.$prime_health_renewals_id.'","'.$call_status.'","'.$sub_disposition.'","'.$ncd.'","'.$remarks.'","'.$payment_mode.'","'.$lapsed_status.'","'.$logged_id.'","'.$created_on.'")';
$renewal_call_log_info = $this->db->query("CALL sp_a_run ('INSERT','$renewal_call_log_qry')");
$renewal_call_log_rslt = $renewal_call_log_info->result();
$renewal_call_log_info->next_result();
$insert_id = $renewal_call_log_rslt[0]->ins_id;
//CALL LOG COUNT UPDATE QUERY TO LIFE RENEWAL TABLE
if($insert_id){
$sel_call_count_qry = 'select call_count from cw_health_renewals where cw_health_renewals.prime_health_renewals_id = "'.$prime_health_renewals_id.'" and trans_status = 1';
$sel_call_count_info = $this->db->query("CALL sp_a_run ('SELECT','$sel_call_count_qry')");
$sel_call_count_rslt = $sel_call_count_info->result();
$sel_call_count_info->next_result();
$renewal_call_count = (int)$sel_call_count_rslt[0]->call_count + 1;
$upd_call_count_qry = 'UPDATE cw_health_renewals SET ncd = "'.$ncd.'",call_count = "'.$renewal_call_count.'",renewal_status = "'.$renewal_status.'",call_status = "'.$call_status.'",sub_disposition = "'.$sub_disposition.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" WHERE prime_health_renewals_id = "'.$prime_health_renewals_id.'" and trans_status = 1';
$this->db->query("CALL sp_a_run ('SELECT','$upd_call_count_qry')");
}
$renewal_call_log_list = $this->renewal_call_log_data($prime_health_renewals_id);
echo json_encode(array('success' => true, 'message' => "Renewals Call Log successfully added", 'renewal_call_log_list' => $renewal_call_log_list));
}else{
$renewal_call_log_upd_qry = 'UPDATE cw_health_renewals_call_log SET call_status = "'.$call_status.'",sub_disposition ="'.$sub_disposition.'",ncd = "'.$ncd.'",remarks = "'.$remarks.'",payment_mode = "'.$payment_mode.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_health_renewals_call_log_id = "'.$prime_health_call_log_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$renewal_call_log_upd_qry')");
$upd_call_count_qry = 'UPDATE cw_health_renewals SET ncd = "'.$ncd.'",renewal_status = "'.$renewal_status.'",call_status = "'.$call_status.'",sub_disposition ="'.$sub_disposition.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" WHERE prime_health_renewals_id = "'.$prime_health_renewals_id.'" and trans_status = 1';
$this->db->query("CALL sp_a_run ('SELECT','$upd_call_count_qry')");
$renewal_call_log_list = $this->renewal_call_log_data($prime_health_renewals_id);
echo json_encode(array('success' => true, 'message' => "Renewals Call Log successfully updated", 'renewal_call_log_list' => $renewal_call_log_list));
}
}
//GET RENEWAL HISTORY
public function renewal_call_log_data($prime_health_renewals_id){
$renewal_call_log_qry = 'select *,cw_health_call_status.health_call_status,cw_sub_disposition.sub_disposition,cw_health_renewals_call_log.trans_created_date as created_date from cw_health_renewals_call_log inner join cw_health_call_status on cw_health_call_status.prime_health_call_status_id = cw_health_renewals_call_log.call_status inner join cw_sub_disposition on cw_sub_disposition.prime_sub_disposition_id=cw_health_renewals_call_log.sub_disposition where cw_health_renewals_call_log.prime_health_renewals_id = "'.$prime_health_renewals_id.'" and cw_health_renewals_call_log.trans_status = 1 order by prime_health_renewals_call_log_id DESC';
$renewal_call_log_info = $this->db->query("CALL sp_a_run ('SELECT','$renewal_call_log_qry')");
$renewal_call_log_rslt = $renewal_call_log_info->result();
$renewal_call_log_info->next_result();
$tr_line = "";
$count = 0;
$rslt_count = count($renewal_call_log_rslt);
foreach($renewal_call_log_rslt as $rslt){
$count++;
$prime_health_call_log_id = $rslt->prime_health_renewals_call_log_id;
// $renewals_type = $rslt->renewal_details;
$call_status = $rslt->health_call_status;
$interested = $rslt->interested;
$not_interested = $rslt->not_interested;
$not_contactable = $rslt->not_contactable;
$other_ways = $rslt->other_ways;
$whatsapp_sent = $rslt->whatsapp_sent;
$mail_sent = $rslt->mail_sent;
$tried_thr_sales_team = $rslt->tried_thr_sales_team;
$noc = $rslt->noc;
$home_visit = $rslt->home_visit;
$in_progress = $rslt->in_progress;
$sub_disposition = $rslt->sub_disposition;
$ncd = date('d-m-Y',strtotime($rslt->ncd));
$call_create_date = date('d-m-Y h:i A',strtotime($rslt->created_date));
// $call_update_date = date('d-m-Y h:i A',strtotime($rslt->trans_updated_date));
//IF CHECK NCD EMPTY OR NOT
if($ncd === '01-01-1970' || $ncd === '00-00-0000' || $ncd === '30-11--0001' || $ncd === '30-11-0001') {
$ncd = '';
}else{
$ncd = date('d-m-Y h:i A',strtotime($rslt->ncd));
}
$remarks = $rslt->remarks;
$payment_mode = $rslt->payment_mode;
$edit_btn = "<a class='btn btn-edit btn-xs row_btn' onclick = health_call_log_edit('$prime_health_call_log_id');>Edit</a>";
$remove_btn = "<a class='btn btn-danger btn-xs row_btn' onclick = health_call_log_remove('$prime_health_call_log_id');>Delete</a>";
$td_btn = "";
// if($rslt_count === $count || $rslt_count === 1){
// $td_btn = "$edit_btn $remove_btn";
// }
if((int)$count === 1){
$td_btn = "$edit_btn $remove_btn";
}
// <td>$count</td>
$tr_line .= "<tr>
<td>$call_status</td>
<td>$sub_disposition</td>
<td>$ncd</td>
<td>$remarks</td>
<td>$call_create_date</td>
<td style='white-space:nowrap;'>$td_btn</td>
</tr>";
}
$renewal_call_log_list = "<table id='health_call_log_list' class='table table-bordered' style='background-color: #FFFFFF; box-shadow: none;'>
<thead>
<tr>
<th>Main Disposition</th>
<th>Sub Disposition</th>
<th>NCD</th>
<th>Remarks</th>
<th>Updated Date</th>
<th>Option</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $renewal_call_log_list;
}
//EDIT LIFE RENEWAL CALL LOG
public function health_call_log_edit(){
$prime_health_call_log_id = (int)$this->input->post('prime_health_call_log_id');
$prime_health_call_log_qry = 'select * from cw_health_renewals_call_log where cw_health_renewals_call_log.prime_health_renewals_call_log_id = "'.$prime_health_call_log_id.'" and trans_status = 1';
// echo $prime_health_call_log_qry;die;
$prime_health_call_log_info = $this->db->query("CALL sp_a_run ('SELECT','$prime_health_call_log_qry')");
$prime_health_call_log_result = $prime_health_call_log_info->result();
$prime_health_call_log_info->next_result();
echo json_encode(array('success' => TRUE, 'prime_health_call_log_result' => $prime_health_call_log_result[0]));
}
//REMOVE LIFE RENEWAL CALL LOG
public function health_call_log_remove(){
$prime_health_call_log_id = (int)$this->input->post('prime_health_call_log_id');
$prime_health_renewals_id = (int)$this->input->post('prime_health_renewals_id');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
$final_delete_qry = 'UPDATE cw_health_renewals_call_log SET trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" , trans_status = 0 WHERE cw_health_renewals_call_log.prime_health_renewals_call_log_id = "'.$prime_health_call_log_id.'"';
$this->db->query("CALL sp_a_run ('SELECT','$final_delete_qry')");
$renewal_call_log_list = $this->renewal_call_log_data($prime_health_renewals_id);
echo json_encode(array('success' => TRUE, 'message' => "Remove Successfully",'renewal_call_log_list' => $renewal_call_log_list));
}
//CANCEL UPDATED UI
public function health_call_log_cancel(){
$prime_health_renewals_id = (int)$this->input->post('prime_health_renewals_id');
$renewal_call_log_list = $this->renewal_call_log_data($prime_health_renewals_id);
echo json_encode(array('success' => TRUE, 'message' => "Cancelled Successfully",'renewal_call_log_list' => $renewal_call_log_list));
}
//GET NEXT DUE DATE
public function update_due_date(){
$renewal_mode = $this->input->post('renewal_mode');
$renewal_from_date = $this->input->post('renewal_from_date');
// $renewal_mode_list = array("1"=>"+1 year","2"=>"+6 months","3"=>"+1 months","4"=>"+2 years","5"=>"+3 months","6"=>"+3 years","7"=>"Fully Paidup");
$renewal_mode_list = array("1"=>"+1 year","2"=>"+6 months","3"=>"+1 months","4"=>"+2 years","5"=>"+3 months","6"=>"+3 years");
// if($renewal_mode === "7"){
// $covered_year = "0";
// $renewal_to_date = date("d-m-Y",strtotime($renewal_from_date));
// }else{
$next_due = $renewal_mode_list[$renewal_mode];
$issued_date = new DateTime($renewal_from_date);
$next_due_date = $issued_date->modify("$next_due");
$next_due_date = $issued_date->modify("-1 days");
$renewal_to_date = $next_due_date->format("d-m-Y");
$policy_from = new DateTime($renewal_from_date);
$policy_from = $policy_from->format("Y-m-d");
$policy_to = new DateTime($renewal_to_date);
$policy_to = $policy_to->modify("+1 day");
$policy_to = $policy_to->format("Y-m-d");
$covered_year = $this->covered_year_cal($renewal_mode,$policy_from,$policy_to);
// if($renewal_mode === "1"){
// $covered_year = "1 Year";
// }else
// if($renewal_mode === "2"){
// $covered_year = "6 Months";
// }else
// if($renewal_mode === "3"){
// $covered_year = "1 Month";
// }else
// if($renewal_mode === "4"){
// $covered_year = "2 Years";
// }else
// if($renewal_mode === "5"){
// $covered_year = "3 Months";
// }else
// if($renewal_mode === "6"){
// $covered_year = "3 Years";
// }
// }
echo json_encode(array('success' => TRUE,'renewal_to_date' => $renewal_to_date,'covered_year' => $covered_year));
}
//get_claim_count query
public function get_claim(){
$health_prime_id = $this->input->post('health_prime_id');
$get_claim_qry = 'select cw_health_claim.patient_name,cw_health_claim.level_one,cw_health_claim.level_two,cw_health_claim.age,cw_health_claim.hospital_name,cw_health_claim.final_diagnosis,cw_health_claim.paid_amount,cw_health_renewals.employee_code,
cw_gender.gender,cw_treatment_type.treatment_type,cw_paid_status.paid_status,cw_health_claim.intimation_no,
cw_health_claim.date_of_admission,cw_health_claim.discharge_date,cw_claim_type.claim_type
FROM cw_health_claim INNER JOIN cw_gender on cw_health_claim.gender = cw_gender.prime_gender_id INNER JOIN cw_treatment_type ON cw_health_claim.treatment_type = cw_treatment_type.prime_treatment_type_id INNER JOIN cw_paid_status ON cw_health_claim.paid_status = cw_paid_status.prime_paid_status_id INNER JOIN cw_claim_type ON cw_health_claim.claim_type = cw_claim_type.prime_claim_type_id INNER JOIN cw_health_renewals ON cw_health_claim.health_renewal_id = cw_health_renewals.prime_health_renewals_id WHERE cw_health_claim.health_renewal_id ='.$health_prime_id.'';
// echo $get_claim_qry;die;
$claim_count_data = $this->db->query($get_claim_qry);
$claim_count_data_rslt = $claim_count_data->result();
echo json_encode(array('success' => TRUE,'get_claim_count' => $claim_count_data_rslt));
}
}
?>