File: /home/cafsindia/login_cafsindia_com/application_bk10feb2023/controllers/Portfolio.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Portfolio extends Action_controller{
public function __construct(){
parent::__construct('portfolio');
$this->collect_base_info();
}
// LOAD PAGE WITH TABLE DATA
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 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;
//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;
$emp_qry = 'select * from cw_employees where prime_employees_id = "'.$this->logged_id.'" and trans_status = 1';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_result = $emp_data->result();
$emp_data->next_result();
$data['emp_result_details'] = json_decode(json_encode($emp_result[0]),true);
$this->load->view("$this->control_name/form",$data);
}
//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);
}
//FUNCTION FOR GET A EXCEL EXPORT MAPPING FORMAT
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));
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH 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);
//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";
$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";
$search_query .= " ORDER BY $order_col $order_sor";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
//echo "BSK $search_query"; die;
$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));
}
//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);
$appl_no = $this->insert_appl_id();
$db_name = $this->config->item("db_name");
//INFORMATION SCHEMA QUERY FOR GET A LIFE RENEWALS TABLE COLUMN NAME
$life_renewals_qry = $this->db->query("SELECT COLUMN_NAME AS col_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = 'cw_life_renewals' and COLUMN_NAME not like '%trans%' and COLUMN_NAME not in ('prime_life_renewals_id','due_date','due_month','due_year','grace_period_date','ncd','call_count','renewal_status','call_status','renewed_date','renewed_month','renewed_year')");
$life_renewals_col = $life_renewals_qry->result_array();
$life_renewals_col_arr = array_map(function($array){
return implode("~",$array);
},$life_renewals_col);
//FOR INSERT A LIFE RENWALS TABLE AS SAME COL NAME OF PORTFOLIO TABLE
$life_renewals_col_name = implode(',', $life_renewals_col_arr);
// echo "<pre>";
// print_r($life_renewals_col_name);die;
$upd_col_name = "";
$form_post_data = array();
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{
if($label_id === "emp_id" && (int)$form_id === 0){
$value = $this->logged_id;
}else
if($label_id === "appl_no" && (int)$form_id === 0){
$value = $appl_no;
}else{
$value = trim($this->input->post($label_id));
}
}
if((int)$field_type === 4){
if($value){
$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(($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,);
}
}else{
$cf_has = true;
$cf_qry_key .= $label_id.",";
$cf_qry_value .= '"'.$value.'",';
$cf_upd_query .= $label_id.' = "'.$value.'",';
if($unique_field === 1){
$cf_unq_chk = $label_id.'= "'.$value.'"';
$query = "select count(*) as rslt_count from $this->cf_table where $this->prime_id != $form_id and $cf_unq_chk";
$unq_chk[] = array('label_id'=>$label_id,'view_name'=>$view_name,'query'=>$query,);
}
}
if($label_id === "issuance_status"){
$issuance_status = (int)$value;
}else
if($label_id === "issuance_date"){
$issued_date = $value;
}else
if($label_id === "mode"){
$mode = $value;
}else
if($label_id === "gst"){
$gst = $value;
}else
if($label_id === "product_name"){
$product_name = $value;
}else
if($label_id === "policy_number"){
$policy_number = $value;
}
if(array_search("$label_id",$life_renewals_col_arr)){
$upd_col_name .= 'cw_life_renewals.'.$label_id.' = cw_portfolio.'.$label_id.',';
}
}
}
$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");
// $next_due_mon = "";
// $next_due_year = "";
//CODES FOR UPDATE THIS DATA TO LIFE RENEWAL INSURANCE TABLE
//ISSUANCE STATUS 2 THEN ADD A DUE DATE AND NEXT DUE DATE
if($issuance_status === 2){
$renewal_mode_list = array("1"=>"+1 year","2"=>"+1 months","3"=>"+6 months","4"=>"+3 months");
$next_due = $renewal_mode_list[$mode];
$issued_date = new DateTime($issued_date);
$next_date = $issued_date;
$due_date = $issued_date->format("Y-m-d");
$next_due_date = $issued_date->modify("$next_due");
$next_due_date = $next_due_date->modify("-1 days");
$next_due_date = $next_due_date->format("Y-m-d");
$next_due_mon = date("F", strtotime("$next_due_date"));
$next_due_year = date("Y", strtotime("$next_due_date"));
// echo "$next_due_date::$next_due_mon::$next_due_year";die;
//QUERY FOR GET A GRACE PERIOD
$product_grace_qry = 'select grace_period from cw_product_name where cw_product_name.prime_product_name_id = "'.$product_name.'" and cw_product_name.trans_status = 1';
$product_grace_info = $this->db->query("CALL sp_a_run ('SELECT','$product_grace_qry')");
$product_grace_rlst = $product_grace_info->result();
$product_grace_info->next_result();
$grace_period = $product_grace_rlst[0]->grace_period;
$next_date_format = $next_date->format("Y-m-d");
$grace_period_date = $next_date->modify("+$grace_period days");
$grace_period_date = $grace_period_date->format("Y-m-d");
$gst_query = 'SELECT gst,renewal_gst from cw_gst where prime_gst_id = "'.$gst.'" and trans_status = 1';
$gst_info = $this->db->query("CALL sp_a_run ('SELECT','$gst_query')");
$gst_rlst = $gst_info->result();
$gst_info->next_result();
$gst_value = $gst_rlst[0]->gst;
$renewal_gst = $gst_rlst[0]->renewal_gst;
}
$portfolio_count = 0;
if($policy_number !== ""){
$select_portfolio_qry = 'SELECT COUNT(*) as count FROM cw_portfolio WHERE cw_portfolio.policy_number = "'.$policy_number.'" and prime_portfolio_id != "'.$form_id.'"';
$select_portfolio_info = $this->db->query("CALL sp_a_run ('SELECT','$select_portfolio_qry')");
$select_portfolio_rlst = $select_portfolio_info->result();
$select_portfolio_info->next_result();
$portfolio_count = (int)$select_portfolio_rlst[0]->count;
}
if($portfolio_count > 0){
echo json_encode(array('success' => FALSE, 'message' => "Same Policy Number Already Exist..."));
exit(0);
}
if((int)$form_id === 0){
$entry_date = date('Y-m-d');
$prime_qry_key .= "trans_created_by,trans_created_date,entry_date";
$prime_qry_value .= '"'.$this->logged_id.'","'.$created_on.'","'.$entry_date.'"';
$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;
if($insert_id && $issuance_status === 2){
//CODE AND BELOW QUERY FOR INSERT A LIFE RENEWALS TABLE
$ins_life_renewals_qry = 'insert into cw_life_renewals ('.$life_renewals_col_name.',due_date,due_month,due_year,grace_period_date,trans_created_by,trans_created_date) SELECT '.$life_renewals_col_name.',"'.$next_due_date.'","'.$next_due_mon.'","'.$next_due_year.'","'.$grace_period_date.'","'.$this->logged_id.'","'.$created_on.'" FROM cw_portfolio where '.$this->prime_table.'.prime_portfolio_id = "'.$insert_id.'"';
//echo $ins_life_renewals_qry; die;
$ins_life_renewals_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_life_renewals_qry')");
$ins_life_renewals_rslt = $ins_life_renewals_info->result();
$ins_life_renewals_info->next_result();
//INSERT A LIFE RENEWAL INSURANCE TABLE
$ins_life_renewal_log_qry = 'insert into cw_life_renewals_log (prime_portfolio_id,prime_life_renewals_id,policy_number,renewal_mode,renewal_due_date,renewal_premium_amount,gst,renewal_gst,renewal_rider,renewal_total_premium,renewal_rider_premium,renewal_next_due_date,from_portfolio,trans_created_by,trans_created_date) SELECT prime_portfolio_id,prime_life_renewals_id,policy_number,mode,"'.$due_date.'",net_premium,"'.$gst_value.'",gst,rider,total_premium,rider_premium,"'.$next_due_date.'","1","'.$this->logged_id.'","'.$created_on.'" FROM cw_life_renewals where cw_life_renewals.prime_portfolio_id = "'.$insert_id.'"';
$ins_life_renewal_log_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_life_renewal_log_qry')");
$ins_life_renewal_log_rslt = $ins_life_renewal_log_info->result();
$ins_life_renewal_log_info->next_result();
}
echo json_encode(array('success' => TRUE, 'message' => "Successfully added", 'insert_id' => $insert_id));
}else{
$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')");
//UPDATE QUERY FOR UPDATE A LIFE RENEWALS TABLE FROM PORTFOLIO TABLE IN SAME PORTFOLIO ID
if($issuance_status === 2){
$select_life_renewal_qry = 'SELECT COUNT(*) as count FROM cw_life_renewals WHERE (prime_portfolio_id = "'.$form_id.'" or cw_life_renewals.policy_number = "'.$policy_number.'")';
$select_life_renewal_info = $this->db->query("CALL sp_a_run ('SELECT','$select_life_renewal_qry')");
$select_life_renewal_rlst = $select_life_renewal_info->result();
$select_life_renewal_info->next_result();
$life_renewal_count = (int)$select_life_renewal_rlst[0]->count;
if(!$life_renewal_count){
// $upd_col_name = rtrim($upd_col_name, ',');
// $upd_life_renewals_qry = 'UPDATE cw_life_renewals INNER JOIN cw_portfolio ON cw_life_renewals.prime_portfolio_id = cw_portfolio.prime_portfolio_id SET '.$upd_col_name.',cw_life_renewals.due_date = "'.$next_due_date.'",cw_life_renewals.due_month = "'.$next_due_mon.'",cw_life_renewals.due_year = "'.$next_due_year.'",cw_life_renewals.grace_period_date = "'.$grace_period_date.'",cw_life_renewals.trans_updated_by = "'. $this->logged_id .'",cw_life_renewals.trans_updated_date = "'.$created_on.'" where cw_life_renewals.prime_portfolio_id = "'.$form_id.'"';
// $upd_life_renewals_info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_life_renewals_qry')");
// //UPDATE QUERY FOR UPDATE A LIFE INSURANCE RENEWAL
// $upd_life_renewals_log_qry = 'UPDATE cw_life_renewals_log INNER JOIN cw_life_renewals ON cw_life_renewals_log.prime_life_renewals_id = cw_life_renewals.prime_life_renewals_id SET cw_life_renewals_log.policy_number = cw_life_renewals.policy_number,cw_life_renewals_log.renewal_mode = cw_life_renewals.mode,cw_life_renewals_log.renewal_due_date = "'.$due_date.'",cw_life_renewals_log.renewal_premium_amount = cw_life_renewals.net_premium,cw_life_renewals_log.gst = "'.$gst_value.'",cw_life_renewals_log.renewal_gst = cw_life_renewals.gst,cw_life_renewals_log.renewal_rider = cw_life_renewals.rider,cw_life_renewals_log.renewal_total_premium = cw_life_renewals.total_premium,cw_life_renewals_log.renewal_rider_premium = cw_life_renewals.rider_premium,cw_life_renewals_log.renewal_next_due_date = "'.$next_due_date.'",cw_life_renewals_log.trans_updated_by = "'. $this->logged_id .'",cw_life_renewals_log.trans_updated_date = "'.$created_on.'" where cw_life_renewals_log.prime_portfolio_id = "'.$form_id.'"';
// $upd_life_renewals_log_info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_life_renewals_log_qry')");
// }else{
//CODE AND BELOW QUERY FOR INSERT A LIFE RENEWALS TABLE
$ins_life_renewals_qry = 'insert into cw_life_renewals ('.$life_renewals_col_name.',due_date,due_month,due_year,grace_period_date,trans_created_by,trans_created_date) SELECT '.$life_renewals_col_name.',"'.$next_due_date.'","'.$next_due_mon.'","'.$next_due_year.'","'.$grace_period_date.'","'.$this->logged_id.'","'.$created_on.'" FROM cw_portfolio where '.$this->prime_table.'.prime_portfolio_id = "'.$form_id.'"';
// echo $ins_life_renewals_qry; die;
$ins_life_renewals_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_life_renewals_qry')");
$ins_life_renewals_rslt = $ins_life_renewals_info->result();
$ins_life_renewals_info->next_result();
//INSERT A LIFE RENEWAL INSURANCE TABLE
$ins_life_renewal_log_qry = 'insert into cw_life_renewals_log (prime_portfolio_id,prime_life_renewals_id,policy_number,renewal_mode,renewal_due_date,renewal_premium_amount,gst,renewal_gst,renewal_rider,renewal_total_premium,renewal_rider_premium,renewal_next_due_date,from_portfolio,trans_created_by,trans_created_date) SELECT prime_portfolio_id,prime_life_renewals_id,policy_number,mode,"'.$due_date.'",net_premium,"'.$gst_value.'",gst,rider,total_premium,rider_premium,"'.$next_due_date.'","1","'.$this->logged_id.'","'.$created_on.'" FROM cw_life_renewals where cw_life_renewals.prime_portfolio_id = "'.$form_id.'"';
$ins_life_renewal_log_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_life_renewal_log_qry')");
$ins_life_renewal_log_rslt = $ins_life_renewal_log_info->result();
$ins_life_renewal_log_info->next_result();
}
}
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated",'insert_id' => $form_id));
}
}
}
//UPDATE STATUS TO DELETE IN MODULE PRIMARY TABLE
public function delete(){
$delete_ids = implode(",",$this->xss_clean($this->input->post('delete_ids')));
$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"));
}
}
//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"));
}
}
//GET COMPANY INFORMATION
public function get_com_info(){
$product_company_name = (int)$this->input->post('product_company_name');
$com_query = 'SELECT prime_product_name_id,product_name,income_proof,check_by,check_amount from cw_product_name where company_name = "'.$product_company_name.'" and trans_status = 1';
$com_info = $this->db->query("CALL sp_a_run ('SELECT','$com_query')");
$com_rlst = $com_info->result();
$com_info->next_result();
$product_info = "<option value=''>---- Product Name ----</option>";
foreach($com_rlst as $result){
$prime_product_name_id = $result->prime_product_name_id;
$product_name = $result->product_name;
$product_info .= "<option value='$prime_product_name_id'>$product_name</option>";
}
echo json_encode(array('success' => TRUE, 'product_info' => $product_info));
}
//GET PRODUCT INFORMATION
public function get_product_info(){
$product_name = (int)$this->input->post('product_name');
$gst_query = 'SELECT prime_gst_id,gst from cw_gst where product_name = "'.$product_name.'" and trans_status = 1';
$gst_info = $this->db->query("CALL sp_a_run ('SELECT','$gst_query')");
$gst_rlst = $gst_info->result();
$gst_info->next_result();
$gst_info = "<option value=''>---- Select ----</option>";
foreach($gst_rlst as $result){
$prime_gst_id = $result->prime_gst_id;
$gst = $result->gst;
$gst_info .= "<option value='$prime_gst_id'>$gst</option>";
}
echo json_encode(array('success' => TRUE, 'gst_info' => $gst_info));
}
//CALCULATION OF TOTAL PREMIUM AMOUNT
public function calaculate_total(){
$total_premium = 0;
$total_sum = 0;
$total_gst = 0;
$gst_val = 0;
$net_premium_val = 0;
$rider_premium_val = 0;
if($this->input->post('rider_premium')){
$rider_premium_val = (int)$this->input->post('rider_premium');
}
if($this->input->post('net_premium')){
$net_premium_val = (int)$this->input->post('net_premium');
}
$gst = (int)$this->input->post('gst');
if($gst){
$gst_query = 'SELECT prime_gst_id,gst from cw_gst where prime_gst_id = "'.$gst.'" and trans_status = 1';
$gst_info = $this->db->query("CALL sp_a_run ('SELECT','$gst_query')");
$gst_rlst = $gst_info->result();
$gst_info->next_result();
$gst_val = $gst_rlst[0]->gst;
}
$total_sum = (int)($net_premium_val + $rider_premium_val);
$total_gst = (int)(($total_sum*$gst_val)/100);
$total_premium = (int)($total_sum + $total_gst);
echo json_encode(array('status' => true,'total_premium' => $total_premium));
}
//GET A OVERALL CREDIT AMOUNT FROM PRODCT AND LOGIN DATE WISE
public function get_credit_amount(){
$emp_id = $this->input->post('emp_id');
$company_name = (int)$this->input->post('company_name');
$product_name = (int)$this->input->post('product_name');
$login_date = $this->input->post('login_date');
$date = date('Y-m-d',strtotime($login_date));
$ppt = (int)$this->input->post('ppt');
$total_net_premium = (int)$this->input->post('total_net_premium');
$designation_channel_qry = "";
$credit_qry = "";
$designation_channel_qry = 'select designation,channel from cw_employees where cw_employees.employee_code = "'.$emp_id.'" and cw_employees.trans_status = 1';
$designation_channel_info = $this->db->query("CALL sp_a_run ('SELECT','$designation_channel_qry')");
$designation_channel_result = $designation_channel_info->result();
$designation_channel_info->next_result();
$designation_id = $designation_channel_result[0]->designation;
$channel_id = $designation_channel_result[0]->channel;
if($designation_id && $channel_id){
$credit_qry = 'select credit,non_credit from cw_portfolio_credit where company_name = "'.$company_name.'" and product_name = "'.$product_name.'" and start_date <="'.$date.'" and end_date >="'.$date.'" and ppt_from <= "'.$ppt.'" and ppt_to >= "'.$ppt.'" and FIND_IN_SET("'.$designation_id.'", designation) and FIND_IN_SET("'.$channel_id.'", channel_name) and trans_status = 1';
$credit_info = $this->db->query("CALL sp_a_run ('SELECT','$credit_qry')");
$credit_result = $credit_info->result();
$credit_info->next_result();
$source_credit = $credit_result[0]->credit;
$non_source_credit = $credit_result[0]->non_credit;
$source_amt = 0;
$non_source_amt = 0;
// if((int)$source_credit_percentage){
// $source_credit_amt = $net_premium * $source_credit_percentage/100;
// echo json_encode(array("status"=>TRUE,"source_credit_amt"=>$source_credit_amt));
// }else{
// $source_credit_amt = 0;
// echo json_encode(array("status"=>FALSE,"source_credit_amt"=>$source_credit_amt,"message"=>"Please Contact Admin"));
// }
if($source_credit === "0" && $non_source_credit === "0"){
$source_amt = 0;
$non_source_amt = 0;
echo json_encode(array("status"=>TRUE,"source_credit_amt" => $source_amt,"non_source_credit_amt" => $non_source_amt));
}else
if((int)$source_credit && (int)$non_source_credit){
$source_amt = $total_net_premium * $source_credit/100;
$non_source_amt = $total_net_premium * $non_source_credit/100;
echo json_encode(array("status"=>TRUE,"source_credit_amt" => $source_amt,"non_source_credit_amt" => $non_source_amt));
}else{
echo json_encode(array("status"=>FALSE,"source_credit_amt"=>$source_amt,"non_source_credit_amt"=>$non_source_amt,"message"=>"Source or Non Source Credit Was Empty.. Please Contact Admin"));
}
}else{
echo json_encode(array("status"=>FALSE, "message"=>"Designation or Channel should not be Empty...!"));
}
}
//CREATE APPLICATION NUMBER
public function insert_appl_id(){
$appl_pre = "CA";
$month = date('Ym');
$this->db->from('appl_no');
$this->db->where('appl_no.trans_status', 1);
$this->db->where('appl_no.year_mon', $month);
$appl_rslt = $this->db->get();
$appl_count = $appl_rslt->num_rows();
$appl_count = (int)$appl_count + 1;
$appl_count = str_pad($appl_count, 4, '0', STR_PAD_LEFT);
$appl_no = $appl_pre.$month.$appl_count;
$this->db->from('appl_no');
$this->db->where('appl_no.trans_status', 1);
$this->db->where('appl_no.year_mon', $month);
$this->db->where('appl_no.appl_no', $appl_no);
$appl_rslt = $this->db->get();
$appl_count = $appl_rslt->num_rows();
if($appl_count === 0){
$appl_data = array(
'appl_no' => $appl_no,
'year_mon' => $month,
'trans_created_by' => $this->session->userdata('logged_id'),
'trans_created_date' => date("Y-m-d H:i:s"),
);
$this->db->insert('appl_no', $appl_data);
return $appl_no;
}else{
$this->insert_appl_id();
}
}
//CHECK INCOME INFO
public function check_income_info(){
$product_name = (int)$this->input->post('product_name');
$sum_assured = $this->input->post('sum_assured');
$net_premium = $this->input->post('net_premium');
$product_info_query = 'SELECT income_proof,check_by,check_amount from cw_product_name where prime_product_name_id = "'.$product_name.'" and trans_status = 1';
$product_info_query = $this->db->query("CALL sp_a_run ('SELECT','$product_info_query')");
$product_info_rlst = $product_info_query->result();
$product_info_query->next_result();
$income_proof = $product_info_rlst[0]->income_proof;
$check_by = $product_info_rlst[0]->check_by;
$check_amount = $product_info_rlst[0]->check_amount;
if((int)$income_proof === 1){
if((int)$check_amount > 0){
if((int)$check_by === 1){
if($sum_assured > $check_amount){
echo json_encode(array('success' => true,'message' => 'Please Check Product Check Amount!!'));
}else{
echo json_encode(array('success' => false,'message' => 'Income proof is not required'));
}
}else
if((int)$check_by === 2){
if($net_premium > $check_amount){
echo json_encode(array('success' => true,'message' => 'Please Check Product Check Amount!!'));
}else{
echo json_encode(array('success' => false,'message' => 'Income proof is not required'));
}
}else{
echo json_encode(array('success' => false,'mis' => true,'message' => 'Please Contact Admin .... !!'));
}
}else{
echo json_encode(array('success' => false,'mis' => true,'message' => 'Please Check Product Check Amount!!'));
}
}else{
echo json_encode(array('success' => false,'message' => 'Income is not required'));
}
}
//CHECK FAMILY DETAILS FOR NOMINEE PERCENTAGE
public function check_family_details(){
$prime_portfolio_id = (int)$this->input->post('prime_form_id');
$family_info_query = 'SELECT IFNULL(sum(percentage),0) as percentage from cw_portfolio_family_details where prime_portfolio_id = "'.$prime_portfolio_id.'" and trans_status = 1';
$family_info_query = $this->db->query("CALL sp_a_run ('SELECT','$family_info_query')");
$family_info_rlst = $family_info_query->result();
$family_info_query->next_result();
$percentage = $family_info_rlst[0]->percentage;
if((int)$percentage === 100){
echo json_encode(array('success' => true ,'message' => 'Can Process'));
}else
if(((int)$percentage < 100) || ((int)$percentage > 100)){
echo json_encode(array('success' => false ,'message' => 'Overall Nominee Percentage should be 100'));
}
}
//SEND MAIL TO CUSTOMER
public function send_email(){//VIEW DATA
$form_view_id = (int)$this->input->post('view_id');
//VIEW DATA
$base_query = str_replace("@SELECT@","cw_employees.employee_code as emp_id,cw_portfolio.appl_no,cw_portfolio.entry_date,".$this->view_select,$this->base_query." inner join cw_employees on cw_portfolio.emp_id = cw_employees.prime_employees_id");
//echo "BSK $this->base_query"; die;
$view_query = $base_query ." where $this->prime_table.$this->prime_id = $form_view_id and $this->prime_table.trans_status = 1";
//echo $base_query; die;
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_query')");
$view_result = $view_data->result();
$view_data->next_result();
$view_result = $view_result[0];
$prime_portfolio_id = $view_result->prime_portfolio_id;
$profile_portfolio_status = $view_result->profile_status;
$assuerd_status = $view_result->assuerd_profile_status;
$proposer_status = $view_result->proposer_concept;
$login_status = $view_result->login_status;
$rm_status = $view_result->rm_status;
if((int)$rm_status === 2 && (int)$login_status === 5){
//AUTO COMPLTE,PICK LIST AND CONDITION
//echo "BSK $rm_status :: $login_status"; die;
foreach($this->form_info as $from){
$prime_form_id = (int)$from->prime_form_id;
$field_type = (int)$from->field_type;
$label_id = $from->label_name;
if(((int)$field_type === 5) || ((int)$field_type === 7) || ((int)$field_type === 9)){
if($view_result->$label_id){
$view_result->$label_id = $this->all_pick[$prime_form_id][$view_result->$label_id];
}
}
}
$row_set_data ="select name,family_date_of_birth,age,cw_relationship.relationship as family_relationship,health,nominee_person,percentage from cw_portfolio_family_details join cw_relationship on cw_relationship.prime_relationship_id = cw_portfolio_family_details.family_relationship where cw_portfolio_family_details.prime_portfolio_id = $prime_portfolio_id and cw_portfolio_family_details.trans_status = 1";
$row_set_info = $this->db->query("CALL sp_a_run ('SELECT','$row_set_data')");
$row_set_rlst = $row_set_info->result();
$row_set_info->next_result();
$family_tr = '';
foreach($row_set_rlst as $rlst){
$name = ucfirst($rlst->name);
$family_date_of_birth = date('d-m-Y',strtotime($rlst->family_date_of_birth));
$age = $rlst->age;
$family_relationship = $rlst->family_relationship;
$health = $rlst->health;
$percentage = $rlst->percentage;
$nominee_person = $rlst->nominee_person;
if((int)$health === 1){
$health = 'Good';
if((int)$nominee_person != 1){
$family_date_of_birth = '-';
$percentage = '-';
}
}else{
$health = 'Passed Away';
if((int)$nominee_person != 1){
$family_date_of_birth = '-';
$percentage = '-';
}
}
$family_tr .= "<tr>
<td style='border:1px dashed #cccccc;'>$family_relationship</td>
<td style='border:1px dashed #cccccc;'>$name</td>
<td style='border:1px dashed #cccccc;'>$age</td>
<td style='border:1px dashed #cccccc;'>$health</td>
<td style='border:1px dashed #cccccc;'>$family_date_of_birth</td>
<td style='border:1px dashed #cccccc;'>$percentage</td>
</tr>";
}
$emp_id = $view_result->emp_id;
$appl_no = $view_result->appl_no;
$entry_date = date('d-m-Y',strtotime($view_result->entry_date));
$lead_source = $view_result->lead_source;
$client_name = ucfirst($view_result->client_name);
$contact_no = $view_result->contact_no;
$crm_updated = $view_result->crm_updated;
$proposer_concept = $view_result->proposer_concept;
$proposer_name = ucfirst($view_result->proposer_name);
$proposer_mobile_no = $view_result->proposer_mobile_no;
$alternative_number = $view_result->proposer_alternative_number;
$assured_alternative = $view_result->life_assured_alter_number;
$proposer_email = strtolower($view_result->proposer_email);
$date_of_birth = date('d-m-Y',strtotime($view_result->date_of_birth));
$gender = $view_result->gender;
$aadhaarno = strtoupper($view_result->aadhaarno);
$pancard_no = strtoupper($view_result->pancard_no);
$profile_status = $view_result->profile_status;
$company_name = ucfirst($view_result->company_name);
$industry = ucfirst($view_result->industry);
$designation = ucfirst($view_result->designation);
$year_of_experience = $view_result->year_of_experience;
$annual_income = $view_result->annual_income;
$education = $view_result->education;
$height = $view_result->height;
$weight = $view_result->weight;
$assured_name = ucfirst($view_result->assured_name);
$assured_mobile_no = $view_result->assured_mobile_no;
$assured_email = strtolower($view_result->assured_email);
$assuerd_date_of_birth = date('d-m-Y',strtotime($view_result->assuerd_date_of_birth));
$assuerd_gender = $view_result->assuerd_gender;
$assuerd_aadhaarno = strtoupper($view_result->assuerd_aadhaarno);
$assuerd_pancard_no = strtoupper($view_result->assuerd_pancard_no);
$assuerd_height = (int)$view_result->assuerd_height;
$assuerd_weight = $view_result->assuerd_weight;
$guardian_name = ucfirst($view_result->guardian_name);
$guardian_date_of_birth = date('d-m-Y',$view_result->guardian_date_of_birth);
$assuerd_profile_status = $view_result->assuerd_profile_status;
$assuerd_company_name = ucfirst($view_result->assuerd_company_name);
$assuerd_industry = ucfirst($view_result->assuerd_industry);
$assuerd_designation = ucfirst($view_result->assuerd_designation);
$assuerd_year_of_experience = $view_result->assuerd_year_of_experience;
$assuerd_annual_income = $view_result->assuerd_annual_income;
$assuerd_education = $view_result->assuerd_education;
$communication_door_flat_no = $view_result->communication_door_flat_no;
$communication_street_name = ucfirst($view_result->communication_street_name);
$communication_area_city = ucfirst($view_result->communication_area_city);
$communication_pincode = $view_result->communication_pincode;
$permanent_door_flat_no = ucfirst($view_result->permanent_door_flat_no);
$permanent_street_name = ucfirst($view_result->permanent_street_name);
$permanent_area_city = ucfirst($view_result->permanent_area_city);
$permanent_pincode = $view_result->permanent_pincode;
$product_company_name = ucfirst($view_result->product_company_name);
$product_name = ucfirst($view_result->product_name);
$ppt = $view_result->ppt;
$term = $view_result->term;
$mode = $view_result->mode;
$rider = $view_result->rider;
$sum_assured = ceil($view_result->sum_assured);
$net_premium = ceil($view_result->net_premium);
$rider_premium = ceil($view_result->rider_premium);
$gst = (int)$view_result->gst;
$total_premium = ceil($view_result->total_premium);
$payment_mode = $view_result->payment_mode;
$doc_status = $view_result->doc_status;
$address_proof = $view_result->address_proof;
$addittional_proof2 = $view_result->addittional_proof2;
$id_proof = $view_result->id_proof;
$photo_copy = $view_result->photo_copy;
$cheque = $view_result->cheque;
$proposer_name_in_cheuqe_leaf = $view_result->proposer_name_in_cheuqe_leaf;
$income_proof = $view_result->income_proof;
$addittional_proof = $view_result->addittional_proof;
$rm_remarks = $view_result->rm_remarks;
$rm_status = $view_result->rm_status;
$mis_remarks = ucfirst($view_result->mis_remarks);
$login_status = $view_result->login_status;
if((int)$profile_portfolio_status === 3){
$company_name = '-';
$industry = '-';
$designation = '-';
$year_of_experience = '-';
$annual_income = '-';
$education = '-';
}
if((int)$assuerd_status === 3){
$assuerd_company_name = '-';
$assuerd_industry = '-';
$assuerd_designation = '-';
$assuerd_year_of_experience = '-';
$assuerd_annual_income = '-';
$assuerd_education = '-';
}
if((int)$proposer_status === 2){
$height = '-';
$weight = '-';
}else
if((int)$proposer_status === 1){
$assured_name = "-";
$assured_mobile_no = "-";
$assured_email = "-";
$assuerd_date_of_birth = "-";
$assuerd_gender = "-";
$assuerd_aadhaarno = "-";
$assuerd_pancard_no = "-";
$guardian_name = "-";
$guardian_date_of_birth = "-";
$assuerd_height = "-";
$assuerd_weight = "-";
$assuerd_profile_status = "-";
$assuerd_company_name = "-";
$assuerd_industry = "-";
$assuerd_designation = "-";
$assuerd_year_of_experience = "-";
$assuerd_annual_income = "-";
$assuerd_education = "-";
}
if($assuerd_date_of_birth != '-'){
$now = time();
$dob = strtotime($assuerd_date_of_birth);
$difference = $now - $dob;
$age = floor($difference / 31556926);
if(!((int)$age < 18)){
$guardian_name = "-";
$guardian_date_of_birth = "-";
}
}
require('./phpmailer/class.phpmailer.php');
$email_content = "<!DOCTYPE html>
<html>
<body style='font-size: 14px; background-color:#f2f2f2;padding:30px;'>
<div style='width: 750px; margin-left: auto; margin-right: auto; padding: 15px; background-color: #ffffff; border-radius: 3px; box-shadow: 0 32px 63px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2) !important;font-family: poppins;'>
<table style='margin-bottom:21px; width:100%;'>
<tr>
<td><img src='https://cafsindia.com/wp-content/uploads/2020/01/CAFS-logo.png' style='max-width: 129px;'></td>
<td style='font-weight:bold;text-align:right;font-size:19px;'>Login Sheet</td>
</tr>
</table>
<table style='width:100%;'>
<tr>
<td style=''>Hi $client_name Welcome!!!</td>
<td style='text-align:right;font-size:12px;'>Application Number: <span style='font-weight:bold;'>$appl_no</span></td>
</tr>
</table>
<table style='width:100%;'>
<tr>
<td style='text-align:right;font-size:12px;'>Login Date: <span style='font-weight:bold;'>$entry_date</span></td>
</tr>
</table>
<table style='width:100%;'>
<tr>
<td style='text-align:right;font-size:12px;'>Login Status: <span style='font-weight:bold;'>$login_status</span></td>
</tr>
</table>
<table style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;font-size: 14px;background-color:#FFFFFF;' border='1' cellspacing='0' cellpadding='4' >
<tbody style='border:1px dashed #cccccc;'>
<tr>
<td colspan='2' style='text-align:center;color: #001630a8;font-weight:bold;border:1px dashed #cccccc;'>Proposer</td>
<td colspan='2' style='text-align:center;color: #001630a8;border:1px dashed #cccccc;font-weight:bold;'>Life Assured</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Name</td>
<td style='border:1px dashed #cccccc;'>$proposer_name</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Name</td>
<td style='border:1px dashed #cccccc;'>$assured_name</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Mobile No.</td>
<td style='border:1px dashed #cccccc;'>$proposer_mobile_no</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Mobile No.</td>
<td style='border:1px dashed #cccccc;'>$assured_mobile_no</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Alternate Mobile No.</td>
<td style='border:1px dashed #cccccc;'>$alternative_number</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Alternate Mobile No.</td>
<td style='border:1px dashed #cccccc;'>$assured_alternative</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Email</td>
<td style='border:1px dashed #cccccc;'>$proposer_email</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Email</td>
<td style='border:1px dashed #cccccc;'>$assured_email</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Date Of Birth</td>
<td style='border:1px dashed #cccccc;'>$date_of_birth</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Date Of Birth</td>
<td style='border:1px dashed #cccccc;'>$assuerd_date_of_birth</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Gender</td>
<td style='border:1px dashed #cccccc;'>$gender</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Gender</td>
<td style='border:1px dashed #cccccc;'>$assuerd_gender</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Aadhaar No.</td>
<td style='border:1px dashed #cccccc;'>$aadhaarno</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Aadhaar No.</td>
<td style='border:1px dashed #cccccc;'>$assuerd_aadhaarno</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>PanCard No.</td>
<td style='border:1px dashed #cccccc;'>$pancard_no</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>PanCard No.</td>
<td style='border:1px dashed #cccccc;'>$assuerd_pancard_no</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Profile Status</td>
<td style='border:1px dashed #cccccc;'>$profile_status</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Profile Status</td>
<td style='border:1px dashed #cccccc;'>$assuerd_profile_status</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Company Name</td>
<td style='border:1px dashed #cccccc;'>$company_name</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Company Name</td>
<td style='border:1px dashed #cccccc;'>$assuerd_company_name</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Industry</td>
<td style='border:1px dashed #cccccc;'>$industry</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Industry</td>
<td style='border:1px dashed #cccccc;'>$assuerd_industry</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Designation</td>
<td style='border:1px dashed #cccccc;'>$designation</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Designation</td>
<td style='border:1px dashed #cccccc;'>$assuerd_designation</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Year Of Experience</td>
<td style='border:1px dashed #cccccc;'>$year_of_experience</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Year Of Experience</td>
<td style='border:1px dashed #cccccc;'>$assuerd_year_of_experience</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Annual Income</td>
<td style='border:1px dashed #cccccc;'>$annual_income</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Annual Income</td>
<td style='border:1px dashed #cccccc;'>$assuerd_annual_income</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Education</td>
<td style='border:1px dashed #cccccc;'>$education</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Education</td>
<td style='border:1px dashed #cccccc;'>$assuerd_education</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Height<sup>(cm)</sup></td>
<td style='border:1px dashed #cccccc;'>$height</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Height<sup>(cm)</sup></td>
<td style='border:1px dashed #cccccc;'>$assuerd_height</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Weight<sup>(kg)</sup></td>
<td style='border:1px dashed #cccccc;'>$weight</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Weight<sup>(kg)</sup></td>
<td style='border:1px dashed #cccccc;'>$assuerd_weight</td>
</tr>
</tbody>
</table>
<h4 style='color: #e1513b;margin-bottom:5px;'>Communication Address</h4>
<table width='100%' style='border-collapse: collapse;border-spacing: 0;border:1px dashed #cccccc !impportant;font-size: 14px;background-color:#FFFFFF;margin-bottom:21px;margin' border='1' cellspacing='0' cellpadding='4' >
<tbody style='border:1px dashed #cccccc;'>
<tr style='color: #001630a8;font-weight:bold;'>
<td style='border:1px dashed #cccccc;'>Door/Flat NO:</td>
<td style='border:1px dashed #cccccc;'>Street Name:</td>
<td style='border:1px dashed #cccccc;'>Area/city:</td>
<td style='border:1px dashed #cccccc;'>Pincode</td>
</tr>
<tr style='border:1px dashed #cccccc;'>
<td style='border:1px dashed #cccccc;'>$communication_door_flat_no</td>
<td style='border:1px dashed #cccccc;'>$communication_street_name</td>
<td style='border:1px dashed #cccccc;'>$communication_area_city</td>
<td style='border:1px dashed #cccccc;'>$communication_pincode</td>
</tr>
</tbody>
</table>
<h4 style='color: #e1513b;margin-bottom:5px;'>Permanent Address</h4>
<table width='100%' style='border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;font-size: 14px;background-color:#FFFFFF;margin-bottom:21px;margin' border='1' cellspacing='0' cellpadding='4' >
<tbody style='border:1px dashed #cccccc;'>
<tr style='font-weight:bold;color: #001630a8;'>
<td style='border:1px dashed #cccccc;'>Door/Flat NO:</td>
<td style='border:1px dashed #cccccc;'>Street Name:</td>
<td style='border:1px dashed #cccccc;'>Area/city:</td>
<td style='border:1px dashed #cccccc;'>Pincode</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;'>$permanent_door_flat_no</td>
<td style='border:1px dashed #cccccc;'>$permanent_street_name</td>
<td style='border:1px dashed #cccccc;'>$permanent_area_city</td>
<td style='border:1px dashed #cccccc;'>$permanent_pincode</td>
</tr>
</tbody>
</table>
<h4 style='color: #e1513b;margin-bottom:5px;'>Family Details</h4>
<style>
.tableclass
{
border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;font-size: 14px;background-color:#FFFFFF;margin-bottom:21px;margin
}
</style>
<table width='100%' class='tableclass' border='1' cellspacing='0' cellpadding='4' >
<tbody style='border:1px dashed #cccccc;'>
<tr style='font-weight:bold; color: #001630a8;'>
<td style=' border:1px dashed #cccccc;'>Relationship</td>
<td style=' border:1px dashed #cccccc;'>Name</td>
<td style=' border:1px dashed #cccccc;'>Age:</td>
<td style=' border:1px dashed #cccccc;'>health</td>
<td style=' border:1px dashed #cccccc;'>DOB</td>
<td style=' border:1px dashed #cccccc;'>Nominee %</td>
</tr>
$family_tr
</tbody>
</table>
<h4 style='color: #e1513b;margin-bottom:5px;'>Product Details</h4>
<table width='100%' style='border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;font-size: 14px;background-color:#FFFFFF;' border='1' cellspacing='0' cellpadding='4' >
<tbody style='border:1px dashed #cccccc;'>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Company Name</td>
<td style='border:1px dashed #cccccc;'>$product_company_name</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Net Premium</td>
<td style='border:1px dashed #cccccc;'>$net_premium</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Product Name</td>
<td style='border:1px dashed #cccccc;'>$product_name</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>GST</td>
<td style='border:1px dashed #cccccc;'>$gst</td>
</tr>
<tr>
<td style='border:1px dashed #cccccc;font-weight:bold;'>PPT</td>
<td style='border:1px dashed #cccccc;'>$ppt</td>
<td style='border:1px dashed #cccccc;font-weight:bold;'>Total Premium</td>
<td style='border:1px dashed #cccccc;'>$total_premium</td>
</tr>
<tr>
<td style=' border:1px dashed #cccccc;font-weight:bold;'>Term</td>
<td style='border:1px dashed #cccccc;'>$term</td>
<td style=' border:1px dashed #cccccc;font-weight:bold;'>Rider Premium</td>
<td style='border:1px dashed #cccccc;'>$rider_premium</td>
</tr>
<tr>
<td style=' border:1px dashed #cccccc;font-weight:bold;'>Mode</td>
<td style='border:1px dashed #cccccc;'>$mode</td>
<td style=' border:1px dashed #cccccc;font-weight:bold;'>Payment Mode</td>
<td style='border:1px dashed #cccccc;'>$payment_mode</td>
</tr>
<tr>
<td style=' border:1px dashed #cccccc;font-weight:bold;'>Sum Assured</td>
<td style='border:1px dashed #cccccc;'>$sum_assured</td>
<td style=' border:1px dashed #cccccc;font-weight:bold;'>Doc Status</td>
<td style='border:1px dashed #cccccc;'>$doc_status</td>
</tr>
</tbody>
</table>
<div>
<h3 style='margin-bottom:0px;'>Terms and Condition</h3>
<p style='font-size:14px;margin-top:0px;'>The accuracy of the document on this website is a system generated. For any issues, please contact CAFS.</p>
</div>
<div style='text-align:center;'>
Designed by <a href='https://www.cafsinfotech.com/' target=_blank; style='font-weight: 600; font-size: 14px;color:#000;text-decoration: none;'><span style='color: #e1513b;'>CAFSInfotech</span></a>
</div>
</div>
</body>
</html>";
$subject = "$appl_no - $client_name - $product_company_name - $net_premium";
//$to_email = "mis@cafsindia.com";//gs@cafsinfotech.com vasanthkumar@cafsinfotech.in
$to_email = "mis@cafsindia.com";
//$to_email = "sathish@cafsinfotech.in";
$send_by = "mf@cafsinfotech.com";
if($to_email !== ""){
$mail = new PHPMailer();
try{
$mail->SMTPDebug = 3;
$mail->IsSMTP();
$mail->Host = "smtp.gmail.com"; // Your SMTP PArameter
$mail->Port = "587"; // Your Outgoing Port
$mail->SMTPAuth = true; // This Must Be True
$mail->Username = $send_by; // Your Email Address
$mail->Password = "welcome@2019"; // Your Password
$mail->SMTPSecure = 'tls'; // Check Your Server's Connections for TLS or SSL
$mail->From = "mf@cafsinfotech.com";
$mail->FromName = "CAFS";
if($to_email){
$mail->AddAddress($to_email);
}
/*if((int)count($bcc) > 0){
foreach ($bcc as $bcc_key => $bcc_mails){
$mail->AddCC($bcc_mails);
}
}*/
$mail->IsHTML(true);
$mail->Subject = $subject;
$mail->Body = $email_content;
//Attachment
$files_sended = '';
$tr_line = '';
if(file_exists($address_proof)){
$mail->addAttachment($address_proof); // Add attachments
$files_sended .= 'Address,';
$tr_line .= "<tr><td>Address</td><td style='color:green !important;'>Address proof Added</td></tr>";
}
if(file_exists($id_proof)){
$mail->addAttachment($id_proof); // Add attachments
$files_sended .= 'Id,';
$tr_line .= "<tr><td>Id proof</td><td style='color:green !important;'>Id proof Added</td></tr>";
}
if(file_exists($photo_copy)){
$mail->addAttachment($photo_copy); // Add attachments
$files_sended .= 'Photo,';
$tr_line .= "<tr><td>Photo proof</td><td style='color:green !important;'>Photo proof Added</td></tr>";
}
if(file_exists($cheque)){
//echo "BSK1";
$mail->addAttachment($cheque); // Add attachments
$files_sended .= 'Cheque,';
$tr_line .= "<tr><td>Cheque</td><td style='color:green !important;'>Cheque Added</td></tr>";
// echo "BSK2";
}
if(file_exists($income_proof)){
$mail->addAttachment($income_proof); // Add attachments
$files_sended .= 'Income,';
$tr_line .= "<tr><td>Income proof</td><td style='color:green !important;'>Income proof Added</td></tr>";
}
if(file_exists($addittional_proof)){
$mail->addAttachment($addittional_proof); // Add attachments
$files_sended .= 'Additional Proof,';
$tr_line .= "<tr><td>Additional proof</td><td style='color:green !important;'>Additional proof Added</td></tr>";
}
if(file_exists($addittional_proof2)){
$mail->addAttachment($addittional_proof2); // Add attachments
$files_sended .= 'Additional Proof2,';
$tr_line .= "<tr><td>Additional proof2</td><td style='color:green !important;'>Additional proof2 Added</td></tr>";
}
$files_sended = rtrim($files_sended,",");
if($mail->addAttachment($cheque)){
$mail= $mail->Send();
if($mail){
$status = 1;
$msg = "Mail Sent Successfully";
$tr_line .= "<tr><td>$emp_code</td><td style='color:green !important;'>$msg</td></tr>";
}
}
}catch(phpmailerException $e){
$status = 0;
$msg = "Mail Not Sent";
$tr_line .= "<tr><td>$emp_code</td><td style='color:#ff0303 !important;'>$msg</td></tr>";
}catch(Exception $e){
$status = 0;
$msg = "Mail Not Sent";
$tr_line .= "<tr><td>$emp_code</td><td style='color:#ff0303 !important;'>$msg</td></tr>";
}
}else{
$status = 0;
$msg = "EMail ID is Empty";
$tr_line .= "<tr><td>$emp_code</td><td style='color:#ff0303 !important;'>$msg</td></tr>";
}
$email_log_query = 'INSERT into cw_email_log (prime_portfolio_id,send_by,send_to,emp_code,status,message) values ("'.$prime_portfolio_id.'","'.$send_by.'","'.$to_email.'","'.$emp_id.'","'.$status.'","'.$msg.'")';
$email_log_info = $this->db->query("CALL sp_a_run ('INSERT','$email_log_query')");
$email_log_result = $email_log_info->result();
$email_log_info->next_result();
if((int)$status === 1){
echo json_encode(array('success' => TRUE,'message' => $tr_line));
}else{
echo json_encode(array('success' => FALSe,'message' => $tr_line));
}
}else{
echo json_encode(array('success' => FALSe,'message' => 'RM Status Or Login Status Not Completed'));
exit();
}
}
//GET A OVERALL CREDIT AMOUNT FROM PRODCT AND LOGIN DATE WISE
public function get_product_credit_details(){
$product_name = (int)$this->input->post('product_name');
$issuance_date = date('Y-m-d',strtotime($this->input->post('issuance_date')));
$issuance_status = (int)$this->input->post('issuance_status');
$net_premium = $this->input->post('net_premium');
$rider = (int)$this->input->post('rider');
$rider_premium = $this->input->post('rider_premium');
$actual_ppt = $this->input->post('ppt');
$actual_term = $this->input->post('term');
$payout_query = 'SELECT * from cw_payout where product_name = "'.$product_name.'" and approval_type = "2" and cw_payout.issued_from_date <= "'. $issuance_date .'" and cw_payout.issued_to_date >= "'. $issuance_date .'" and trans_status = 1 ';
$payout_info = $this->db->query("CALL sp_a_run ('SELECT','$payout_query')");
$payout_rlst = $payout_info->result();
$payout_info->next_result();
if($payout_rlst[0]){
//IF RIDER YES TO BE CALCULATE RIDER BASE AND ORC CREDIT AMOUNT
if($rider === 1){
$rider_payout_query = 'SELECT * from cw_rider_payout where product_name = "'.$product_name.'" and approval_type = "2" and cw_rider_payout.issued_from_date <= "'. $issuance_date .'" and cw_rider_payout.issued_to_date >= "'. $issuance_date .'" and trans_status = 1';
$rider_payout_info = $this->db->query("CALL sp_a_run ('SELECT','$rider_payout_query')");
$rider_payout_rlst = $rider_payout_info->result();
$rider_payout_info->next_result();
if($rider_payout_rlst[0]){
echo json_encode(array("status" => TRUE, "payout_rlst" => $payout_rlst, "rider_payout_rlst" => $rider_payout_rlst, "message" => 'Proceed'));
}else{
echo json_encode(array("status" => FALSE, "message" => "Please Check? Rider Payout Details not Mapped for this Product...!"));
}
}else{
echo json_encode(array("status" => TRUE, "payout_rlst" => $payout_rlst, "message" => 'Proceed'));
}
}else{
echo json_encode(array("status" => FALSE, "message" => "Please Map the Payout Details...!"));
}
}
}
?>