File: /home/cafsindia/login_cafsindia_com/application/controllers/Report_controller.php
<?php
/**********************************************************
Filename: Report_controller.php
Description: Report Controller for all module report is generator.
Author: Jaffer Sathik
Created on: 13 March 2019
Reviewed by:
Reviewed on:
Approved by:
Approved on:
-------------------------------------------------------
Modification Details
Changed by:
Change Info:
-------------------------------------------------------
***********************************************************/
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
require_once("Secure_Controller.php");
abstract class Report_controller extends Secure_Controller{
public $control_name;
public $logged_id;
public $logged_role;
public $report_id;
public $report_data = array();
public $table_info = array();
public $form_info = array();
public $fliter_list = array();
public $all_pick = array();
public $select_query = "";
public $base_query = "" ;
public $where_query = "" ;
public $pick_query = "";
public $prime_table = "";
public $pdf_file_name = "";
public function __construct($module_id = NULL){
parent::__construct($module_id);
$this->control_name = strtolower($this->router->fetch_class());
$this->logged_id = $this->session->userdata('logged_id');
$this->logged_role = $this->session->userdata('logged_role');
}
//USER ACCESS THE REPORT CHECKING
public function isvalid($report_id){
$this->report_id = $report_id;
$valid_user_qry = 'select * from cw_report_setting where trans_status = 1 and prime_report_setting_id = "'.$report_id.'" and FIND_IN_SET("'.$this->logged_role.'",report_for)';
$valid_user_info = $this->db->query("CALL sp_a_run ('SELECT','$valid_user_qry')");
$valid_user_result = $valid_user_info->result();
$user_count = $valid_user_info->num_rows();
$valid_user_info->next_result();
if($user_count){
$this->collect_base_info();
return true;
}else{
return false;
}
}
public function collect_base_info(){
$this->get_base_query();
$this->get_table_header_info();
$this->select_query = rtrim($this->select_query,',');
$this->fliter_list = json_decode(json_encode($this->fliter_list));
$this->table_info = json_decode(json_encode($this->table_info));
$this->form_info = json_decode(json_encode($this->form_info));
}
public function get_base_query(){
$report_setting_query = 'select * from cw_report_setting where prime_report_setting_id = "'.$this->report_id.'" and trans_status = "1"';
$report_setting_data = $this->db->query("CALL sp_a_run ('SELECT','$report_setting_query')");
$report_setting_result = $report_setting_data->result();
$report_setting_data->next_result();
if($report_setting_result){
$prime_report_setting_id = $report_setting_result[0]->prime_report_setting_id;
$report_name = $report_setting_result[0]->report_name;
$report_for = $report_setting_result[0]->report_for;
$report_tables = $report_setting_result[0]->report_tables;
$report_columns = $report_setting_result[0]->report_columns;
$report_prime_column = $report_setting_result[0]->report_prime_column;
$report_group_by = $report_setting_result[0]->report_group_by;
$search_filter = $report_setting_result[0]->search_filter;
$date_filter = $report_setting_result[0]->date_filter;
$date_filter_column = $report_setting_result[0]->date_filter_column;
$subtotal_filter = $report_setting_result[0]->subtotal_filter;
$subtotal_column = $report_setting_result[0]->subtotal_column;
$report_view_query = 'select * from cw_report_view where report_setting_id = "'.$this->report_id.'" and trans_status = "1"';
$report_view_data = $this->db->query("CALL sp_a_run ('SELECT','$report_view_query')");
$report_view_count = $report_view_data->num_rows();
$report_view_data->next_result();
$this->report_data[] = array('report_name'=>$report_name,'report_prime_column'=>$report_prime_column,'search_filter'=>$search_filter,'date_filter'=>$date_filter,'date_filter_column'=>$date_filter_column,'subtotal_filter'=>$subtotal_filter,'subtotal_column'=>$subtotal_column,'report_group_by'=>$report_group_by,'report_view_count'=>$report_view_count,);
$this->report_data = json_decode(json_encode($this->report_data));
$this->select_query = "$report_prime_column,";
$table_count = explode(",",$report_tables);
$tab_count = count($table_count);
if((int)$tab_count > 1){
//WHERE TABLE JOIN DATA
$table_query = 'select * from cw_report_table where trans_status = 1 and join_for = "'.$this->report_id.'" ORDER BY abs(line_sort) asc';
$table_data = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$table_result = $table_data->result();
$table_data->next_result();
$count =0;
foreach($table_result as $table){
$count++;
if((int)$count === 1){
$this->prime_table = $table->line_prime_table;
}
$line_prime_table = $table->line_prime_table;
$line_prime_col = $table->line_prime_col;
$line_join_type = $table->line_join_type;
$line_join_table = $table->line_join_table;
$line_join_col = $table->line_join_col;
$line_sort = $table->line_sort;
$prime_table = $table->line_prime_table;
$module_name = str_replace("cw_","",$line_prime_table);
$prime_id = "prime_".$module_name."_id";
//$cf_id = "prime_".$module_name."_cf_id";
//$cf_table_name = $this->db->dbprefix($module_name."_cf");
$join_module_name = str_replace("cw_","",$line_join_table);
$join_prime_id = "prime_".$join_module_name."_id";
//$join_cf_id = "prime_".$join_module_name."_cf_id";
//$join_cf_table_name = $this->db->dbprefix($join_module_name."_cf");
if((int)$line_sort === 1){
$line_table_query .= " $line_prime_table $line_join_type join $line_join_table on $line_join_col = $line_prime_col";
}else{
$line_table_query .= " $line_join_type join $line_join_table on $line_join_col = $line_prime_col";
}
}
}else{
$module_name = str_replace("cw_","",$report_tables);
$prime_id = "prime_".$module_name."_id";
//$cf_id = "prime_".$module_name."_cf_id";
//$cf_table_name = $this->db->dbprefix($module_name."_cf");
$line_table_query = " $report_tables ";
$prime_table = $this->db->dbprefix($module_name);
$this->prime_table = $prime_table;
}
$this->base_query = "select @SELECT from $line_table_query";
$where_condition = "";
$table_search_query = 'select where_condition from cw_report_where where where_for_id = "'.$this->report_id.'" and trans_status = "1"';
$table_search_data = $this->db->query("CALL sp_a_run ('SELECT','$table_search_query')");
$table_search_result = $table_search_data->result();
$table_search_data->next_result();
if($table_search_result){
$where_condition = str_replace('^','"',$table_search_result[0]->where_condition);
$get_val = 1;
if((int)$this->logged_role === 12){
$get_val = 2;
}
$session_query = 'select session_value from cw_session_value where session_for = "'.$get_val.'" and trans_status = "1"';
$session_data = $this->db->query("CALL sp_a_run ('SELECT','$session_query')");
$session_result = $session_data->result();
$session_data->next_result();
foreach($session_result as $rslt){
$session_value = $rslt->session_value;
if($session_value !== "access_data"){
$exist_val = "@".$session_value."@";
$saved_session_val = $this->session->userdata($session_value);
$where_condition = str_replace($exist_val,$saved_session_val,$where_condition);
}
}
$this->where_query = $where_condition;
}
}
}
public function get_table_header_info(){
$table_header_qry = 'select select_column,table_mode from cw_report_table_view where trans_status = 1 and custom_select_report_id="'.$this->report_id.'" order by table_sort asc';
$table_header_info = $this->db->query("CALL sp_a_run ('SELECT','$table_header_qry')");
$table_header_rslt = $table_header_info->result();
$table_header_info->next_result();
$array_list = array();
foreach($table_header_rslt as $header_rslt){
$select_column = $header_rslt->select_column;
$table_mode = (int)$header_rslt->table_mode;
if($table_mode === 1){
$select_column = explode(".",$select_column);
$module_name = $select_column[0];
$module_id = str_replace("cw_","",$module_name);
//$module_id = str_replace("_cf","",$module_id);
$label_name = $select_column[1];
$form_setting_qry = 'select * from cw_form_setting where trans_status = 1 and prime_module_id = "'.$module_id.'" and label_name = "'.$label_name.'"';
$form_setting_info = $this->db->query("CALL sp_a_run ('SELECT','$form_setting_qry')");
$form_setting_result = $form_setting_info->result();
$form_setting_info->next_result();
if($form_setting_result){
$form_rslt = $form_setting_result[0];
$prime_form_id = (int)$form_rslt->prime_form_id;
$prime_module_id = $form_rslt->prime_module_id;
$input_view_type = (int)$form_rslt->input_view_type;
$input_for = (int)$form_rslt->input_for;
$field_type = (int)$form_rslt->field_type;
$label_id = $form_rslt->label_name;
$label_name = ucwords($form_rslt->view_name);
$field_length = $form_rslt->field_length;
$field_decimals = $form_rslt->field_decimals;
$pick_list_type = (int)$form_rslt->pick_list_type;
$pick_list = $form_rslt->pick_list;
$pick_table = $form_rslt->pick_table;
$auto_prime_id = $form_rslt->auto_prime_id;
$auto_dispaly_value = $form_rslt->auto_dispaly_value;
$field_isdefault = (int)$form_rslt->field_isdefault;
$file_type = (int)$form_rslt->file_type;
$mandatory_field = (int)$form_rslt->mandatory_field;
$unique_field = (int)$form_rslt->unique_field;
$search_show = (int)$form_rslt->search_show;
if(($label_id ==="level_1_reporting_person") || ($label_id ==="level_2_reporting_person") || ($label_id ==="level_2_reporting_designation")){
$array_list = array();
}
$table_name = "cw_".$prime_module_id;
//$cf_table_name = "cw_".$prime_module_id."_cf";
if($field_isdefault === 1){
$pick_sel_table = $table_name;
}else
if($field_isdefault === 2){
$pick_sel_table = $cf_table_name;
}
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query = "select $pick_list from $pick_table where trans_status = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
$this->all_pick[$prime_form_id] = $array_list;
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
$this->select_query .= "$pick_query_as.$pick_list_val_2 as $label_id,";
$this->pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $pick_sel_table.$label_id ";
}
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query = "select $pick_list_val_1,$pick_list_val_2 from $pick_table where $pick_list_val_3 = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
$this->all_pick[$prime_form_id] = $array_list;
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
$this->select_query .= "$pick_query_as.$pick_list_val_2 as $label_id,";
$this->pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $pick_sel_table.$label_id ";
}
}
$filter_label_id = $pick_sel_table.".".$label_id;
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
$this->select_query .= "$pick_query_as.$auto_dispaly_value as $label_id,";
$this->pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $pick_sel_table.$label_id ";
$filter_label_id = $pick_query_as.".".$auto_dispaly_value;
}
}else{
if(($input_view_type === 1) || ($input_view_type === 2)){
if($field_isdefault === 1){
$this->select_query .= "$table_name.$label_id,";
$filter_label_id = $table_name.".".$label_id;
}else
if($field_isdefault === 2){
$this->select_query .= "$cf_table_name.$label_id,";
$filter_label_id = $cf_table_name.".".$label_id;
}
}
}
$this->fliter_list[] = array('label_id'=> $label_id, 'label_name'=> $label_name, 'field_isdefault'=> $field_isdefault, 'array_list'=> $array_list, 'field_type'=> $field_type,'filter_label_id'=>$filter_label_id);
$this->table_info[] = array('field_type'=>$field_type,'label_name'=>$label_id,'view_name'=>$label_name);
$this->form_info[] = array('prime_form_id'=>$prime_form_id,'prime_module_id'=>$prime_module_id,'field_type'=>$field_type,'pick_list'=>$pick_list,'pick_table'=>$pick_table,'input_view_type'=>$input_view_type,'auto_prime_id'=>$auto_prime_id,'auto_dispaly_value'=>$auto_dispaly_value,'label_id'=>$label_id, 'field_isdefault'=> $field_isdefault,'pick_list_type'=>$pick_list_type);
}
}else
if($table_mode === 2){
$custom_select_qry = 'select select_column_query from cw_report_custom_select where trans_status = 1 and custom_select_report_id="'.$this->report_id.'" and custom_column_name = "'.$select_column.'"';
$custom_select_info = $this->db->query("CALL sp_a_run ('SELECT','$custom_select_qry')");
$custom_select_rslt = $custom_select_info->result();
$custom_select_info->next_result();
$select_column_query = $custom_select_rslt[0]->select_column_query;
$select_column_query = rtrim($select_column_query,',');
$select_column_query = ltrim($select_column_query,',');
$select_column_query = str_replace("@","",$select_column_query);
$select_column_query = str_replace("^",'"',$select_column_query);
$this->select_query .= "$select_column_query,";
$this->table_info[] = array('field_type'=>"custom",'label_name'=>$select_column,'view_name'=>ucwords(str_replace("_"," ",$select_column)));
}
}
}
public function get_report_view($input_vals){
$report_view_list = array();
$view_size_list = array(1=>"col-md-3",2=>"col-md-4",3=>"col-md-6",4=>"col-md-12");
$report_view_query = 'select * from cw_report_view where report_setting_id = "'.$this->report_id.'" and trans_status = "1" order by abs(view_sort)';
$report_view_data = $this->db->query("CALL sp_a_run ('SELECT','$report_view_query')");
$report_view_rslt = $report_view_data->result();
$report_view_data->next_result();
foreach($report_view_rslt as $view_rslt){
$prime_view_id = (int)$view_rslt->prime_view_id;
$view_name = ucwords($view_rslt->view_name);
$view_type = $view_rslt->view_type;
$view_size = $view_rslt->view_size;
$view_chart = $view_rslt->view_chart;
$view_chart_size = $view_rslt->view_chart_size;
$view_tables = $view_rslt->view_tables;
$view_columns = $view_rslt->view_columns;
$view_size = $view_size_list[$view_size];
$view_chart_size = $view_size_list[$view_chart_size];
$report_view_list[$view_name]['info'] = $view_rslt;
$select_query = "";
$line_table_query = "";
$pick_query = "";
$join_query = "";
// GET BLOCK QUERY
$trans_table = array();
$table_count = explode(",",$view_tables);
$tab_count = count($table_count);
if((int)$tab_count > 1){
$table_query = 'select * from cw_report_view_table where trans_status = 1 and join_for = "'.$prime_view_id.'" ORDER BY abs(line_sort) asc';
$table_data = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$table_result = $table_data->result();
$table_data->next_result();
foreach($table_result as $table){
$line_prime_table = $table->line_prime_table;
$line_prime_col = $table->line_prime_col;
$line_join_type = $table->line_join_type;
$line_join_table = $table->line_join_table;
$line_join_col = $table->line_join_col;
$line_sort = $table->line_sort;
$prime_table = $table->line_prime_table;
$module_name = str_replace("cw_","",$line_prime_table);
$prime_id = "prime_".$module_name."_id";
//$cf_id = "prime_".$module_name."_cf_id";
//$cf_table_name = $this->db->dbprefix($module_name."_cf");
$join_module_name = str_replace("cw_","",$line_join_table);
$join_prime_id = "prime_".$join_module_name."_id";
//$join_cf_id = "prime_".$join_module_name."_cf_id";
//$join_cf_table_name = $this->db->dbprefix($join_module_name."_cf");
$trans_table[$line_prime_table] = $line_prime_table;
$trans_table[$line_join_table] = $line_join_table;
if((int)$line_sort === 1){
$line_table_query .= " $line_prime_table $line_join_type join $line_join_table on $line_join_col = $line_prime_col ";
}else{
$line_table_query .= " $line_join_type join $line_join_table on $line_join_col = $line_prime_col";
}
}
}else{
$module_name = str_replace("cw_","",$view_tables);
$prime_id = "prime_".$module_name."_id";
//$cf_id = "prime_".$module_name."_cf_id";
//$cf_table_name = $this->db->dbprefix($module_name."_cf");
$trans_table[$view_tables] = $view_tables;
$line_table_query = " $view_tables ";
}
//GET PICK QUERY
$from_info = array();
$column_qry = 'select view_column,table_mode from cw_report_view_table_sort where custom_view_id = "'.$prime_view_id.'" and trans_status = 1 order by abs(table_sort)';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$column_qry')");
$column_result = $column_info->result();
$column_info->next_result();
foreach($column_result as $query_columns){
$view_column = $query_columns->view_column;
$table_mode = (int)$query_columns->table_mode;
if($table_mode === 1){
$query_columns = explode(".",$view_column);
$module_name = $query_columns[0];
$module_id = str_replace("cw_","",$module_name);
//$module_id = str_replace("_cf","",$module_id);
$label_name = $query_columns[1];
$form_setting_qry = 'select * from cw_form_setting where trans_status = 1 and prime_module_id = "'.$module_id.'" and label_name = "'.$label_name.'"';
$form_setting_info = $this->db->query("CALL sp_a_run ('SELECT','$form_setting_qry')");
$form_setting_result = $form_setting_info->result();
$form_setting_info->next_result();
if($form_setting_result){
$form_rslt = $form_setting_result[0];
$prime_form_id = (int)$form_rslt->prime_form_id;
$prime_module_id = $form_rslt->prime_module_id;
$input_view_type = (int)$form_rslt->input_view_type;
$input_for = (int)$form_rslt->input_for;
$field_type = (int)$form_rslt->field_type;
$label_id = $form_rslt->label_name;
$label_name = ucwords($form_rslt->view_name);
$short_name = $form_rslt->short_name;
$field_length = $form_rslt->field_length;
$field_decimals = $form_rslt->field_decimals;
$pick_list_type = (int)$form_rslt->pick_list_type;
$pick_list = $form_rslt->pick_list;
$pick_table = $form_rslt->pick_table;
$auto_prime_id = $form_rslt->auto_prime_id;
$auto_dispaly_value = $form_rslt->auto_dispaly_value;
$field_isdefault = (int)$form_rslt->field_isdefault;
$file_type = (int)$form_rslt->file_type;
$mandatory_field = (int)$form_rslt->mandatory_field;
$unique_field = (int)$form_rslt->unique_field;
$search_show = (int)$form_rslt->search_show;
if($short_name){
$label_name = $short_name;
}
$from_info[] = array("label_id"=>$label_id,"label_name"=>$label_name,"field_type"=>$field_type);
$table_name = "cw_".$prime_module_id;
//$cf_table_name = "cw_".$prime_module_id."_cf";
if($field_isdefault === 1){
$pick_sel_table = $table_name;
}else
if($field_isdefault === 2){
$pick_sel_table = $cf_table_name;
}
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_id,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $pick_sel_table.$label_id ";
}
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_id,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $pick_sel_table.$label_id ";
}
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_id,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $pick_sel_table.$label_id ";
}
}else{
if(($input_view_type === 1) || ($input_view_type === 2)){
if($field_isdefault === 1){
$select_query .= "$table_name.$label_id,";
}else
if($field_isdefault === 2){
$select_query .= "$cf_table_name.$label_id,";
}
}
}
}
}
}
//CUSTOME SELECT
$custom_select_qry = 'select * from cw_report_view_select where trans_status = 1 and view_block_id = "'.$prime_view_id.'"';
$custom_select_info = $this->db->query("CALL sp_a_run ('SELECT','$custom_select_qry')");
$custom_select_result = $custom_select_info->result();
$custom_select_info->next_result();
$custom_select = "";
foreach($custom_select_result as $custom_rslt){
$view_column_name = $custom_rslt->view_column_name;
$view_select_column_query = $custom_rslt->view_select_column_query;
$select_query .= str_replace("@","",$view_select_column_query).",";
$from_info[] = array("label_id"=>$view_column_name,"label_name"=>ucwords(str_replace("_"," ",$view_column_name)),"field_type"=>"CUSTOM");
}
// WHERE CONDITION
$where_setting_qry = 'select * from cw_report_view_map where trans_status = 1 and report_view_id = "'.$prime_view_id.'" and trans_status = 1';
$where_setting_info = $this->db->query("CALL sp_a_run ('SELECT','$where_setting_qry')");
$where_setting_result = $where_setting_info->result();
$where_setting_info->next_result();
$where_query = "where ";
foreach($trans_table as $trans){
$where_query .= $trans.".trans_status = 1 and ";
}
foreach($where_setting_result as $where_rslt){
$prime_column = $where_rslt->prime_column;
$map_column = $where_rslt->map_column;
if($input_vals[$prime_column]){
$map_column_val = $input_vals[$prime_column];
$where_query .= $map_column.' = "'.$map_column_val.'" and ';
}
}
$where_query = rtrim($where_query,' and ');
$select_query = rtrim($select_query,',');
$join_query = "$line_table_query $pick_query $where_query ";
$report_block_query = "select ".$select_query." from ".$join_query;
if((int)$this->report_id === 1){
if($view_name === "Investments Summary"){
$report_view_list[$view_name]['mode'] = "CUSTOM";
$report_view_list[$view_name]['rslt'] = $this->investments_summary($prime_view_id,$input_vals);
}else{
$report_block_data = $this->db->query("CALL sp_a_run ('SELECT','$report_block_query')");
$report_block_rslt = $report_block_data->result();
$report_block_data->next_result();
$rslt_array = array();
foreach($report_block_rslt as $block_rslt){
$value_list_array = array();
foreach($from_info as $label_info){
$label_id = $label_info['label_id'];
$value = $block_rslt->$label_id;
$field_type = (int)$label_info['field_type'];
if($field_type === 4){
$value = date('d-m-Y',strtotime($value));
}
$value_list_array[$label_id] = $value;
}
$rslt_array[] = $value_list_array;
}
$report_view_list[$view_name]['mode'] = "DYNAMIC";
$report_view_list[$view_name]['rslt'] = $rslt_array;
$report_view_list[$view_name]['from_info'] = $from_info;
}
}else{
$report_block_data = $this->db->query("CALL sp_a_run ('SELECT','$report_block_query')");
$report_block_rslt = $report_block_data->result();
$report_block_data->next_result();
$rslt_array = array();
foreach($report_block_rslt as $block_rslt){
$value_list_array = array();
foreach($from_info as $label_info){
$label_id = $label_info['label_id'];
$value = $block_rslt->$label_id;
$field_type = (int)$label_info['field_type'];
if($field_type === 4){
$value = date('d-m-Y',strtotime($value));
}
$value_list_array[$label_id] = $value;
}
$rslt_array[] = $value_list_array;
}
$report_view_list[$view_name]['mode'] = "DYNAMIC";
$report_view_list[$view_name]['rslt'] = $rslt_array;
$report_view_list[$view_name]['from_info'] = $from_info;
}
}
if((int)$this->report_id === 1){
$report_view_content = $this->get_consolidated_view_content($report_view_list,$input_vals);
}else{
$report_view_content = $this->get_report_view_content($report_view_list,$input_vals);
}
$file_name = "";
foreach($input_vals as $key=>$value){
$file_name .= $value."_";
}
$pdf_file_name = date('dmY')."_".rtrim($file_name,'_').".pdf";
$report_name = str_replace(" ","_",strtolower($this->report_data[0]->report_name));
$file_path = "./download_pdf/$report_name/$pdf_file_name";
return array('final_content'=>$report_view_content,'pdf_file_name'=>$pdf_file_name,'file_path'=>$file_path);
}
// REPORT VIEW CONTENT
public function get_report_view_content($report_view_list,$input_vals){
print_r($input_vals);
print_r($report_view_list);
}
// CONSOLIDATED VIEW CONTENT
public function get_consolidated_view_content($report_view_list,$input_vals){
$group_code = "";
foreach($input_vals as $cust_key => $cust_map){
if($cust_key === "cw_customer.group_code"){
$group_code = $cust_map;
}
}
$cust_info_query = 'select group_head,short_name,pan_number,aadhar_number,group_code,email,managed_by,residence_address,residence_city,cw_state.state_name as residence_state,residence_pincode,cw_country.country_name as residence_country,cw_employees.employee_name,cw_employees.mobile_number as rm_mobile_number from cw_customer left join cw_state on prime_state_id = residence_state left join cw_country on prime_country_id = residence_country left join cw_employees on prime_employees_id = managed_by where cw_customer.trans_status = "1" and map_group = "'.$group_code.'"';
$cust_info_data = $this->db->query("CALL sp_a_run ('SELECT','$cust_info_query')");
$cust_info_rslt = $cust_info_data->result();
$cust_info_data->next_result();
$family_list = array();
$group_head_info = array();
foreach($cust_info_rslt as $cust_info){
$group_head = (int)$cust_info->group_head;
$family_list[] = $cust_info->short_name;
if($group_head === 1){
$short_name = ucwords($cust_info->short_name);
$pan_number = $cust_info->pan_number;
$aadhar_number = $cust_info->aadhar_number;
$group_code = $cust_info->group_code;
$email = $cust_info->email;
$managed_by = $cust_info->managed_by;
$mobile_number = $cust_info->customer_mobile_number;
$residence_address = ucwords($cust_info->residence_address);
$residence_city = ucwords($cust_info->residence_city);
$residence_state = ucwords($cust_info->residence_state);
$residence_pincode = $cust_info->residence_pincode;
$residence_country = ucwords($cust_info->residence_country);
$employee_name = ucwords($cust_info->employee_name);
$rm_mobile_number = $cust_info->rm_mobile_number;
$qr_info = "Name:".ucwords($cust_info->short_name)." GroupCode:".$cust_info->group_code." RM Manager:".ucwords($cust_info->employee_name)."-".$cust_info->rm_mobile_number;
$report_name = str_replace(" ","_",strtolower($this->report_data[0]->report_name));
$file_name = str_replace(" ","_",strtolower($short_name)).".png";
$file_path = "./download_pdf/$report_name/$file_name";
$url = urlencode($qr_info);
$width = 100;
$height = 100;
$image = 'http://chart.apis.google.com/chart?chs='.$width.'x'.$height.'&cht=qr&chl='.$url;
$file = file_get_contents($image);
header('Content-type: image/png;');
header("Content-Disposition: attachment; filename=$file_name");
header("Cache-Control: public");
unlink("$file_path");
file_put_contents($file_path, $file);
$qr_img = "<img id='final_qr' src='$file_path' style='margin-bottom:2px;'/>";
$report_date = date("d-m-Y");
$cust_info_table = "<table cellspacing='0' cellpadding='0' style='border-bottom: 2px solid gray;'>
<tr>
<td style='width: 100px; vertical-align: middle;'>
<img src='./images/cafs.png' alt='CAFS' style='height:85px;'>
</td>
<td>
<span style='color: #00b0eb; font-size:inherit; font-weight: bold;'>$short_name</span><br/>
$email<br/>
$residence_address,$residence_city<br/>
$residence_state - $residence_pincode<br/>
RM: $employee_name - $rm_mobile_number<br/>
</td>
<td style='text-align: right; vertical-align: middle;'>
Date:$report_date<br/>
$qr_img
</td>
</tr>
</table>";
}
}
$split_up_head = array("savings"=>"Saving","life_premium"=>"Life Insurance","ulip"=>"ULIP","bullion"=>"Bullion","property"=>"Property","mutual_fund"=>"Mutual Fund","stock"=>"Stock","others"=>"Others");
$networth_head = array("networth"=>"Networth","life_premium"=>"Premium","life_death_benefit"=>"Cover","health_premium"=>"Premium","health_cover"=>"Cover");
$image_array = array("Bullion"=>'bullion.png',"Saving"=>'bank.png',"Property"=>'bank.png',"Life Insurance"=>'life_insurance.png',"Stock"=>'stocks.png',"Mutual Fund"=>'mutual_fund.png',"ULIP"=>'ulip.png',"Others"=>'stocks.png');
foreach($report_view_list as $view_key => $view_data){
$view_info = $view_data['info'];
$view_rslt = $view_data['rslt'];
$from_info = $view_data['from_info'];
if($view_key === "Investments Summary"){
$inverstment_summary = $view_rslt['inverstment_summary'];
$family_networth = $view_rslt['family_networth'];
$family_split_up = $view_rslt['family_split_up'];
// SUMMARY TABLE
$summary_tr_line = "";
$summary_chart_data = [];
$summary_count = 0;
foreach($inverstment_summary as $summary){
$summary_count++;
$summary_name = $summary['name'];
$summary_as_on = $summary['as_on'];
$summary_value = $summary['value'];
$td_style = "";
if($summary_count === 1){
$td_style = "border-top: 1px dashed gray";
}
$pdf_icons = "";
if($image_array[$summary_name]){
$image_name = $image_array[$summary_name];
$pdf_icons = "<img src='./images/pdf_icons/$image_name' alt='$name' style='height: 20px;'/>";
}
$tr_style = "";
if($summary_name === "Total"){
$tr_style = "class='tot_tr'";
}else{
$summary_chart_data['data'][] = array("name"=>$summary_name,"y"=>$summary_value);
}
$summary_tr_line .= "<tr $tr_style>
<td style='white-space: nowrap;font-weight:bold;$td_style'>$pdf_icons $summary_name</td>
<td style='white-space: nowrap;$td_style'>$summary_as_on</td>
<td style='white-space: nowrap;$td_style'>$summary_value</td>
</tr>";
}
$rslt = array();
array_push($rslt,$summary_chart_data);
$summary_content ="<table cellspacing='0' cellpadding='0' class='pdf_table'>
$summary_tr_line
</table>";
$chart_info = array('render_to'=>'summary_chart','title'=>ucwords($view_key),'legend'=>'true','chart_type'=>1,'chart_data'=>$summary_chart_data);
$summary_chart_content = $this->get_chart_view($chart_info);
//NETWORTH TABLE
$networth_tr_line = "";
$memberwise_tr_line = "";
$networth_chart_data = [];
foreach($family_list as $family){
$total_networth = $family_networth['total_networth'];
$family_name = ucwords($family);
if($family_networth[$family]){
$networth_data = $family_networth[$family];
$networth = $networth_data['networth'];
if(!$networth){ $networth = 0; }
$life_premium = $networth_data['life_premium'];
if(!$life_premium){ $life_premium = 0; }
$life_conver = $networth_data['life_death_benefit'];
if(!$life_conver){ $life_conver = 0; }
$health_premium = $networth_data['health_premium'];
if(!$health_premium){ $health_premium = 0; }
$health_cover = $networth_data['health_cover'];
if(!$health_cover){ $health_cover = 0; }
$yearly_commitment = 0;
$yearly_commitment_info = $networth_data['yearly_commitment'];
foreach($yearly_commitment_info as $yearly_key=>$yearly_value){
$yearly_commitment += (int)$yearly_value;
}
$networth_percentage = round(((int)$networth/(int)$total_networth)* 100,2);
$chart_name = substr($family_name,0,8)."...";
$networth_chart_data['data'][] = array("name"=>$chart_name,"y"=>$networth);
$networth_tr_line .= "<tr>
<td>$family_name</td>
<td>$networth</td>
<td>$life_premium</td>
<td>$life_conver</td>
<td>$health_premium</td>
<td>$health_cover</td>
<td>$yearly_commitment</td>
<td>$networth_percentage%</td>
</tr>";
}else{
$networth_tr_line .= "<tr>
<td>$family_name</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>";
}
$split_up_td ="<td>$family_name</td>";
if($family_split_up[$family]){
$split_up = $family_split_up[$family];
foreach($split_up_head as $head_key=>$head){
$value = 0;
if($split_up[$head_key]){
$value = $split_up[$head_key];
}
$split_up_td .="<td>$value</td>";
}
}else{
foreach($split_up_head as $head_key=>$head){
$value = 0;
$split_up_td .="<td>$value</td>";
}
}
$memberwise_tr_line .="<tr>$split_up_td</tr>";
}
$rslt = array();
$style = array('fontSize'=>'9px;','textShadow'=>false,'textOutline'=>false);
//$networth_chart_data['dataLabels'] = array('enabled'=>true,'format'=>'{point.y:.1f}','align'=>'right','rotation'=>'-90','style'=>$style);
array_push($rslt,$networth_chart_data);
$chart_info = array('render_to'=>'networth_chart','title'=>'Family Networth','legend'=>'false','chart_type'=>2,'chart_data'=>$networth_chart_data);
$networth_chart_content = $this->get_chart_view($chart_info);
$networth_content = "<table cellspacing='0' cellpadding='0' class='pdf_table'>
<colgroup><col>
</colgroup><colgroup span='2'></colgroup>
<colgroup span='2'></colgroup>
<thead>
<tr>
<th rowspan='2'>Family Members</th>
<th rowspan='2'>Net Worth</th>
<th colspan='2' scope='colgroup' >Life Insurance</th>
<th colspan='2' scope='colgroup' >Health Insurance</th>
<th rowspan='2'>Yearly Commitment</th>
<th rowspan='2'>Percentage</th>
</tr>
<tr>
<th scope='col'>Premium</th>
<th scope='col'>Covered</th>
<th scope='col'>Premium</th>
<th scope='col'>Covered</th>
</tr>
</thead>
<tbody>
$networth_tr_line
</tbody>
</table>";
$split_head_td .="<th>Family Members</th>";
foreach($split_up_head as $head_key=>$head){
$split_head_td .="<th >$head</th>";
}
$memberswise_head = "<tr>$split_head_td</tr>";
$memberswise_content = "<table cellspacing='0' cellpadding='0' class='pdf_table'>
<thead>
$memberswise_head
</thead>
<tbody>
$memberwise_tr_line
</tbody>
</table>";
//MEMBERWISE TABLE
$investments_content = "<h5 style='color: #00b0eb; font-size: inherit;font-weight:bold; margin-top: 10.5px; margin-bottom: 10.5px;'>$view_key</h5>
<table>
<tr>
<td class='summary_td'>$summary_content</td>
<td style='text-align:center;' id='summary_chart'> chart content </td>
</tr>
</table>
<script> $summary_chart_content </script>
<h5 style='color: #00b0eb; font-size: inherit;font-weight:bold; margin-top: 10.5px; margin-bottom: 10.5px;'>Memberwise Breakup</h5>
<table style='margin-bottom:15px;'>
<tr>
<td style='min-height:200px;width:265px;text-align:center;' id='networth_chart' class='networth_td'> chart content </td>
<td style='vertical-align:top;'>$networth_content</td>
</tr>
</table>
<script> $networth_chart_content </script>
$memberswise_content";
}else{
$view_total_columns = explode(",",$view_info->view_total_columns);
$total_array = array();
foreach($view_total_columns as $total_colums){
$total_colums = explode(".",$total_colums);
$total_col_name = $total_colums[1];
$total_array[$total_col_name] = 0;
}
$content_th = "<tr>$content_th</tr>";
$content_tr = "";
foreach($view_rslt as $data_set){
$content_td = "";
foreach($data_set as $table_key => $table_val){
$content_td .="<td>$table_val</td>";
if(array_key_exists($table_key, $total_array)) {
$total_val = $total_array[$table_key];
$total_array[$table_key] = (int)$total_val + (int)$table_val;
}
}
$content_tr .= "<tr>$content_td</tr>";
}
$content_th = "";
$total_td = "";
$count = 0;
foreach($from_info as $view_head){
$count++;
$label_id = $view_head['label_id'];
$head_name = ucwords($view_head['label_name']);
$content_th .= "<th>$head_name</th>";
if(array_key_exists($label_id, $total_array)) {
$total_val = $total_array[$label_id];
$total_td .= "<td>$total_val</td>";
}else{
$tot_name = "";
if($count === 1){
$tot_name = "Total";
}
$total_td .= "<td>$tot_name</td>";
}
}
$content_tr .= "<tr class='tot_tr'>$total_td</tr>";
$head_info = "<tr>
<td colspan='$count' style='border: 0px !important; text-align: left;background-color: #FFFFFF;'>
<h5 style='color: #00b0eb; font-size: inherit;font-weight:bold; margin-top: 10.5px; margin-bottom: 10.5px;'>$view_key</h5>
</td>
</tr>";
$view_content .= "<div>
<table cellspacing='0' cellpadding='0' class='pdf_table'>
<thead>
$head_info
$content_th
</thead>
<tbody>
$content_tr
</tbody>
</table><div>";
}
}
$table_content = "<div id='page_content'>
<div id='content' style='background-color: #FFFFFF;padding:5px;'>
$cust_info_table
$investments_content
$view_content
</div>
<div id='footer' style='border-top: 2px solid gray;font-size: 11px;background-color: #FFFFFF;'>
<p style='text-align:center;margin:0px;'>The amount indicated contains guaranteed and non guaranteed elements, Hence the actual amount receivable may vary from above</p>
<table>
<tr>
<td style='text-align:left;padding:4px;'> <b>For Escalation :</b> info@cafs.co.in / 044-48583669/ +919884639855 </td>
<td style='text-align:right;padding:4px;'> <b>Prepared by :</b> cafsinfotech.com </td>
</tr>
</table>
</div>
</div>";
return $table_content;
}
public function investments_summary($prime_view_id,$input_vals){
$where_setting_qry = 'select * from cw_report_view_map where trans_status = 1 and report_view_id = "'.$prime_view_id.'" and trans_status = 1';
$where_setting_info = $this->db->query("CALL sp_a_run ('SELECT','$where_setting_qry')");
$where_setting_result = $where_setting_info->result();
$where_setting_info->next_result();
$where_query = "";
foreach($where_setting_result as $where_rslt){
$prime_column = $where_rslt->prime_column;
$map_column = $where_rslt->map_column;
if($input_vals[$prime_column]){
$map_column_val = $input_vals[$prime_column];
$where_query .= $map_column.' = "'.$map_column_val.'" and ';
}
}
$where_query = rtrim($where_query,' and ');
$inverstment_rlst = array();
$today = date("d-m-Y");
$year_array = array(1=>0,2=>12,3=>4,4=>2,5=>1);
$total_inverstment = 0;
$total_networth = 0;
//SAVING INFORMATION
$today_date = date("Y-m-d");
$bank_where = $where_query.' and cw_bank_deposit.maturity_date >= "'.$today_date.'" order by depositor_name,deposit_scheme';
$bank_query = 'SELECT short_name,depositor_name,deposit_scheme,principal_amount,compounding,start_date,tenure_value,tenure_mode,rate_of_return,maturity_date,maturity_amount FROM cw_customer LEFT JOIN cw_bank_deposit ON cw_bank_deposit.depositor_name = cw_customer.prime_customer_id where cw_bank_deposit.trans_status = 1 AND '.$bank_where;
$bank_data = $this->db->query("CALL sp_a_run ('SELECT','$bank_query')");
$bank_rslt = $bank_data->result();
$bank_data->next_result();
$saving_total_val = 0;
foreach($bank_rslt as $bank){
$short_name = $bank->short_name;
$depositor_name = $bank->depositor_name;
$deposit_scheme = (int)$bank->deposit_scheme;
$principal_amount = $bank->principal_amount;
$compounding = $bank->compounding;
$start_date = $bank->start_date;
$tenure_value = $bank->tenure_value;
$tenure_mode = $bank->tenure_mode;
$rate_of_return = $bank->rate_of_return;
$maturity_date = $bank->maturity_date;
$maturity_amount = $bank->maturity_amount;
if($deposit_scheme === 1){
$start_date = new datetime($start_date);
$begin_date = $start_date->format('Y-m-d');
$bank_maturity_date = new datetime($today_date);
$bank_maturity_date = $bank_maturity_date->format('Y-m-d');
$date_parts1 = explode('-', $begin_date);
$date_parts2 = explode('-', $bank_maturity_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]);
$years = abs($end_date - $start_date);
$compounding = $year_array[$compounding];
$interst_rate = $rate_of_return/100;
$years = $years/365;
$temp = (1+$interst_rate/$compounding);
$maturity_amount = round($principal_amount*pow($temp,($compounding*$years)));
}else
if($deposit_scheme === 2){
$start_date = new datetime($start_date);
$bank_maturity_date = new datetime($today_date);
$tot_month = $start_date->diff($bank_maturity_date)->m + ($start_date->diff($bank_maturity_date)->y*12);
$ror1 = $rate_of_return/100;
$ror = $ror1/12;
$maturity_amount = round($principal_amount*pow(1 + $ror,$tot_month));
}else
if($deposit_scheme === 3){
$start_date = new datetime($start_date);
$bank_maturity_date = new datetime($today_date);
$months = $start_date->diff($bank_maturity_date)->m + ($start_date->diff($bank_maturity_date)->y*12);
$compounding = $year_array[$compounding];
$irate = $rate_of_return/$compounding;
$year = $months/12;
$maturity_amount =($principal_amount *(pow((1+ $irate/100),($year)*$compounding)-1)/(1-pow((1+$irate/100),-$compounding/12)));
$maturity_amount = round($maturity_amount);
}else
if($deposit_scheme === 4){
$start_date = new datetime($start_date);
$bank_maturity_date = new datetime($today_date);
$tot_month = $start_date->diff($bank_maturity_date)->m + ($start_date->diff($bank_maturity_date)->y*12);
$ror1 = $rate_of_return/100;
$ror = $ror1/12;
$maturity_amount = $principal_amount*pow(1 + $ror,$tot_month);
$maturity_amount = round($maturity_amount);
}else
if($deposit_scheme === 5){
$start_date = new datetime($start_date);
$bank_maturity_date = new datetime($today_date);
$tot_month = $start_date->diff($bank_maturity_date)->m + ($start_date->diff($bank_maturity_date)->y*12);
$ror1 = $rate_of_return/100;
$ror = $ror1/12;
$maturity_amount = $principal_amount*pow(1 + $ror,$tot_month);
$maturity_amount = round($maturity_amount);
}
$savings = 0;
if($inverstment_rlst["family_split_up"][$short_name]['savings']){
$savings = $inverstment_rlst["family_split_up"][$short_name]['savings'];
}
$inverstment_rlst["family_split_up"][$short_name]['savings'] = (int)$savings + (int)$maturity_amount;
$networth = 0;
if($inverstment_rlst["family_networth"][$short_name]['networth']){
$networth = $inverstment_rlst["family_networth"][$short_name]['networth'];
}
$inverstment_rlst["family_networth"][$short_name]['networth'] = (int)$networth + (int)$maturity_amount;
$saving_total_val += (int)$maturity_amount;
}
$gov_where = $where_query.' and cw_government_investments.maturity_date >= "'.$today_date.'" order by holder,scheme';
$gov_query = 'SELECT short_name,holder,scheme,paying_type,amount,start_date,maturity_date,rate_of_return,maturity_value FROM cw_customer LEFT JOIN cw_government_investments ON cw_government_investments.holder = cw_customer.prime_customer_id where cw_government_investments.trans_status = 1 AND '.$gov_where;
$gov_data = $this->db->query("CALL sp_a_run ('SELECT','$gov_query')");
$gov_rslt = $gov_data->result();
$gov_data->next_result();
foreach($gov_rslt as $gov){
$short_name = $gov->short_name;
$holder = $gov->holder;
$scheme = $gov->scheme;
$paying_type = $gov->paying_type;
$amount = $gov->amount;
$start_date = $gov->start_date;
$maturity_date = $gov->maturity_date;
$rate_of_return = $gov->rate_of_return;
$maturity_value = $gov->maturity_value;
$begin_date = new datetime($start_date);
$begin_date = $begin_date->format('Y-m-d');
$end_date = new datetime($today_date);
$end_date = $end_date->format('Y-m-d');
$date_parts1 = explode('-', $begin_date);
$date_parts2 = explode('-', $end_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){
$years = 1;
}else{
$years = floor($diff / 365.25);
}
$savings = 0;
if($inverstment_rlst["family_split_up"][$short_name]['savings']){
$savings = $inverstment_rlst["family_split_up"][$short_name]['savings'];
}
$inverstment_rlst["family_split_up"][$short_name]['savings'] = (int)$savings + (int)$maturity_amount;
$networth = 0;
if($inverstment_rlst["family_networth"][$short_name]['networth']){
$networth = $inverstment_rlst["family_networth"][$short_name]['networth'];
}
$inverstment_rlst["family_networth"][$short_name]['networth'] = (int)$networth + (int)$maturity_amount;
$saving_total_val += (int)$maturity_amount;
}
$inv_where = $where_query.' and cw_other_investments.maturity_date >= "'.$today_date.'" order by investor';
$inv_query = 'SELECT short_name,investor,description,paying_type,amount,tenure_value,tenure,expected_returns,maturity_date,maturity_value,start_date FROM cw_customer LEFT JOIN cw_other_investments ON cw_other_investments.investor = cw_customer.prime_customer_id where cw_other_investments.trans_status = 1 AND '.$inv_where;
$inv_data = $this->db->query("CALL sp_a_run ('SELECT','$inv_query')");
$inv_rslt = $inv_data->result();
$inv_data->next_result();
foreach($inv_rslt as $inv){
$short_name = $inv->short_name;
$investor = $inv->investor;
$description = $inv->description;
$paying_type = $inv->paying_type;
$amount = $inv->amount;
$tenure_value = $inv->tenure_value;
$tenure = $inv->tenure;
$expected_returns = $inv->expected_returns;
$maturity_date = $inv->maturity_date;
$maturity_value = $inv->maturity_value;
$start_date = $inv->start_date;
$begin_date = new datetime($start_date);
$begin_date = $begin_date->format('Y-m-d');
$end_date = new datetime($today_date);
$end_date = $end_date->format('Y-m-d');
$date_parts1 = explode('-', $begin_date);
$date_parts2 = explode('-', $end_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){
$years = 1;
}else{
$years = floor($diff / 365.25);
}
$maturity_amount = round($amount * pow(1 + ($expected_returns/100),$years), 2);
$savings = 0;
if($inverstment_rlst["family_split_up"][$short_name]['savings']){
$savings = $inverstment_rlst["family_split_up"][$short_name]['savings'];
}
$inverstment_rlst["family_split_up"][$short_name]['savings'] = (int)$savings + (int)$maturity_amount;
$networth = 0;
if($inverstment_rlst["family_networth"][$short_name]['networth']){
$networth = $inverstment_rlst["family_networth"][$short_name]['networth'];
}
$inverstment_rlst["family_networth"][$short_name]['networth'] = (int)$networth + (int)$maturity_amount;
$saving_total_val += (int)$maturity_amount;
}
$total_inverstment += (int)$saving_total_val;
$total_networth += (int)$saving_total_val;
$inverstment_rlst["inverstment_summary"][] = array("name"=>"Saving","as_on"=>"(as on $today)","value"=>$saving_total_val);
//LIFE INSURANCE INFORMATION
$life_where = $where_query.' order by life_assured_name';
$life_insurance_query = 'SELECT short_name,cw_life_insurance.policy_no,cw_life_insurance.life_assured_name, cw_life_insurance.issued_date,cw_life_insurance.term,cw_life_insurance.ppt,cw_life_insurance_renewal.renewal_mode,cw_life_insurance_renewal.renewal_total_premium,cw_life_insurance.death_benefit,cw_life_insurance_renewal.renewal_policy_id FROM cw_customer LEFT JOIN cw_life_insurance ON cw_life_insurance.life_assured_name = cw_customer.prime_customer_id LEFT JOIN cw_life_insurance_renewal ON cw_life_insurance_renewal.renewal_policy_id = cw_life_insurance.prime_life_insurance_id WHERE cw_life_insurance.trans_status = 1 AND cw_life_insurance_renewal.trans_status = 1 AND '.$life_where;
$life_insurance_data = $this->db->query("CALL sp_a_run ('SELECT','$life_insurance_query')");
$life_insurance_rslt = $life_insurance_data->result();
$life_insurance_data->next_result();
$life_total_val = 0;
$life_cover_array = array();
foreach($life_insurance_rslt as $life){
$short_name = $life->short_name;
$policy_no = $life->policy_no;
$life_assured_name = $life->life_assured_name;
$issued_date = $life->issued_date;
$term = $life->term;
$ppt = $life->ppt;
$renewal_mode = $life->renewal_mode;
$renewal_total_premium = $life->renewal_total_premium;
$death_benefit = $life->death_benefit;
$expected_returns = $life->expected_returns;
$maturity_date = $life->maturity_date;
$maturity_value = $life->maturity_value;
$start_date = $life->start_date;
$monthly = array(1=>"2",2=>"1",3=>"12",4=>"11",5=>"10",6=>"9",7=>"8",8=>"7",9=>"6",10=>"5",11=>"4",12=>"3");
$quarterly = array(1=>"4",2=>"4",3=>"4",4=>"3",5=>"3",6=>"3",7=>"2",8=>"2",9=>"2",10=>"1",11=>"1",12=>"1");
$semi_annual = array(1=>"2",2=>"2",3=>"2",4=>"3",5=>"1",6=>"1",7=>"1",8=>"1",9=>"1",10=>"2",11=>"2",12=>"2");
$start_date = new datetime($issued_date);
$start_year = $start_date->format('Y');
$end_date = new datetime();
$end_year = $end_date->format('Y');
$end_month = $end_date->format('m');
$yearly_commitment = 0;
if($start_year === $end_year){
if((int)$renewal_mode === 2){
$yearly_commitment = (int)$renewal_total_premium * (int)$monthly[(int)$end_month];
}else
if((int)$renewal_mode === 3){
$yearly_commitment = (int)$renewal_total_premium * (int)$quarterly[(int)$end_month];
}else
if((int)$renewal_mode === 4){
$yearly_commitment = (int)$renewal_total_premium * (int)$semi_annual[(int)$end_month];
}else
if((int)$renewal_mode === 5){
$yearly_commitment = (int)$renewal_total_premium;
}
}else{
if((int)$renewal_mode !== 1){
$yearly_commitment = (int)$renewal_total_premium * (int)$year_array[$renewal_mode];
}
}
if(!$inverstment_rlst["family_networth"][$short_name]['yearly_commitment'][$policy_no]){
$inverstment_rlst["family_networth"][$short_name]['yearly_commitment'][$policy_no] = $yearly_commitment;
}
$life_premium = 0;
if($inverstment_rlst["family_split_up"][$short_name]['life_premium']){
$life_premium = $inverstment_rlst["family_split_up"][$short_name]['life_premium'];
}
$inverstment_rlst["family_split_up"][$short_name]['life_premium'] = (int)$life_premium + (int)$renewal_total_premium;
$life_premium = 0;
if($inverstment_rlst["family_networth"][$short_name]['life_premium']){
$life_premium = $inverstment_rlst["family_networth"][$short_name]['life_premium'];
}
$inverstment_rlst["family_networth"][$short_name]['life_premium'] = (int)$life_premium + (int)$renewal_total_premium;
$life_total_val += (int)$renewal_total_premium;
$life_cover_array[$policy_no] = array('short_name'=>$short_name,'death_benefit'=>$death_benefit);
}
foreach($life_cover_array as $cover_array){
$short_name = $cover_array['short_name'];
$death_benefit = $cover_array['death_benefit'];
$life_death_benefit = 0;
if($inverstment_rlst["family_networth"][$short_name]['life_death_benefit']){
$life_death_benefit = $inverstment_rlst["family_networth"][$short_name]['life_death_benefit'];
}
$inverstment_rlst["family_networth"][$short_name]['life_death_benefit'] = (int)$life_death_benefit + (int)$death_benefit;
}
$total_inverstment += (int)$life_total_val;
$inverstment_rlst["inverstment_summary"][] = array("name"=>"Life Insurance","as_on"=>"(Premium Paid)","value"=>$life_total_val);
//HEALTH INSURANCE INFORMATION
$health_where = $where_query.' order by customer';
$health_insurance_query = 'SELECT short_name,cw_health_insurance.prime_health_insurance_id,cw_health_insurance_renewal.renewal_premium_amount,cw_sum_assured.sum_assured FROM cw_customer LEFT JOIN cw_health_insurance ON cw_health_insurance.customer = cw_customer.prime_customer_id LEFT JOIN cw_health_insurance_renewal ON cw_health_insurance_renewal.renewal_policy_id = cw_health_insurance.prime_health_insurance_id left join cw_sum_assured on cw_health_insurance.sum_assured = cw_sum_assured.prime_sum_assured_id WHERE cw_health_insurance.trans_status = 1 AND cw_health_insurance_renewal.trans_status = 1 AND '.$health_where;
$health_insurance_data = $this->db->query("CALL sp_a_run ('SELECT','$health_insurance_query')");
$health_insurance_rslt = $health_insurance_data->result();
$health_insurance_data->next_result();
$health_total_val = 0;
$health_cover_array = array();
foreach($health_insurance_rslt as $health){
$short_name = $health->short_name;
$prime_health_insurance_id = $health->prime_health_insurance_id;
$renewal_premium_amount = $health->renewal_premium_amount;
$sum_assured = $health->sum_assured;
if(!$inverstment_rlst["family_networth"][$short_name]['yearly_commitment'][$prime_health_insurance_id]){
$inverstment_rlst["family_networth"][$short_name]['yearly_commitment'][$prime_health_insurance_id] = $renewal_premium_amount;
}
$health_premium = 0;
if($inverstment_rlst["family_networth"][$short_name]['health_premium']){
$health_premium = $inverstment_rlst["family_networth"][$short_name]['health_premium'];
}
$inverstment_rlst["family_networth"][$short_name]['health_premium'] = (int)$health_premium + (int)$renewal_premium_amount;
$health_total_val += $renewal_premium_amount;
$health_cover_array[$prime_health_insurance_id] = array('short_name'=>$short_name,'sum_assured'=>$sum_assured);
}
foreach($health_cover_array as $cover_array){
$short_name = $cover_array['short_name'];
$sum_assured = $cover_array['sum_assured'];
$health_cover = 0;
if($inverstment_rlst["family_networth"][$short_name]['health_cover']){
$health_cover = $inverstment_rlst["family_networth"][$short_name]['health_cover'];
}
$inverstment_rlst["family_networth"][$short_name]['health_cover'] = (int)$health_cover + (int)$sum_assured;
}
//ULIP INFORMATION
$ulip_total_val = 0;
$total_inverstment += (int)$ulip_total_val;
$total_networth += (int)$ulip_total_val;
$inverstment_rlst["inverstment_summary"][] = array("name"=>"ULIP","as_on"=>"(as on $today)","value"=>$ulip_total_val);
//BULLION INFORMATION
$bullion_where = $where_query.' order by customer_name,metal';
$bullion_query = 'SELECT short_name,customer_name,metal,quantity_in_gms,purchase_price,date as purchase_date,total_price FROM cw_customer LEFT JOIN cw_bullion ON cw_bullion.customer_name = cw_customer.prime_customer_id where cw_bullion.trans_status = 1 AND '.$bullion_where;
$bullion_data = $this->db->query("CALL sp_a_run ('SELECT','$bullion_query')");
$bullion_rslt = $bullion_data->result();
$bullion_data->next_result();
$bullion_total_val = 0;
foreach($bullion_rslt as $bullion){
$short_name = $bullion->short_name;
$customer_name = $bullion->customer_name;
$metal = $bullion->metal;
$quantity_in_gms = $bullion->quantity_in_gms;
$purchase_price = $bullion->purchase_price;
$purchase_date = $bullion->purchase_date;
$total_price = $bullion->total_price;
$bullion_total_price = 0;
if($inverstment_rlst["family_split_up"][$short_name]['bullion']){
$bullion_total_price = $inverstment_rlst["family_split_up"][$short_name]['bullion'];
}
$inverstment_rlst["family_split_up"][$short_name]['bullion'] = (int)$bullion_total_price + (int)$total_price;
$networth = 0;
if($inverstment_rlst["family_networth"][$short_name]['networth']){
$networth = $inverstment_rlst["family_networth"][$short_name]['networth'];
}
$inverstment_rlst["family_networth"][$short_name]['networth'] = (int)$networth + (int)$total_price;
$bullion_total_val += (int)$total_price;
}
$total_inverstment += $bullion_total_val;
$total_networth += $bullion_total_val;
$inverstment_rlst["inverstment_summary"][] = array("name"=>"Bullion","as_on"=>"(as on $today)","value"=>$bullion_total_val);
//PROPERTY INFORMATION
$property_where = $where_query.' order by customer_name,property_type';
$property_query = 'SELECT short_name,customer_name,property_type,purchase_price,purchase_date,latest_value FROM cw_customer LEFT JOIN cw_property ON cw_property.customer_name = cw_customer.prime_customer_id where cw_property.trans_status = 1 AND '.$property_where;
$property_data = $this->db->query("CALL sp_a_run ('SELECT','$property_query')");
$property_rslt = $property_data->result();
$property_data->next_result();
$property_total_val = 0;
foreach($property_rslt as $property){
$short_name = $property->short_name;
$customer_name = $property->customer_name;
$property_type = $property->property_type;
$purchase_price = $property->purchase_price;
$purchase_price = $property->purchase_price;
$purchase_date = $property->purchase_date;
$latest_value = $property->latest_value;
$property_latest_value = 0;
if($inverstment_rlst["family_split_up"][$short_name]['property']){
$property_latest_value = $inverstment_rlst["family_split_up"][$short_name]['property'];
}
$inverstment_rlst["family_split_up"][$short_name]['property'] = (int)$property_latest_value + (int)$latest_value;
$networth = 0;
if($inverstment_rlst["family_networth"][$short_name]['networth']){
$networth = $inverstment_rlst["family_networth"][$short_name]['networth'];
}
$inverstment_rlst["family_networth"][$short_name]['networth'] = (int)$networth + (int)$latest_value;
$property_total_val += (int)$latest_value;
}
$total_inverstment += (int)$property_total_val;
$total_networth += (int)$property_total_val;
$inverstment_rlst["inverstment_summary"][] = array("name"=>"Property","as_on"=>"(as on $today)","value"=>$property_total_val);
//MUTUAL FUND INFORMATION
$mf_total_val = 0;
$total_inverstment += (int)$mf_total_val;
$total_networth += (int)$mf_total_val;
$inverstment_rlst["inverstment_summary"][] = array("name"=>"Mutual Fund","as_on"=>"(as on $today)","value"=>$mf_total_val);
//STOCK INFORMATION
$stock_where = $where_query.' order by customer_name,quantity';
$stock_query = 'SELECT short_name,stock_name,quantity,avg_invest_price FROM cw_customer LEFT JOIN cw_stock ON cw_stock.customer_name = cw_customer.prime_customer_id WHERE cw_stock.trans_status = 1 AND '.$stock_where;
$stock_data = $this->db->query("CALL sp_a_run ('SELECT','$stock_query')");
$stock_rslt = $stock_data->result();
$stock_data->next_result();
$stock_total_val = 0;
foreach($stock_rslt as $stock){
$short_name = $stock->short_name;
$stock_name = $stock->stock_name;
$quantity = $stock->quantity;
$avg_invest_price = $stock->avg_invest_price;
$stock_avg_invest_price = 0;
if($inverstment_rlst["family_split_up"][$short_name]['stock']){
$stock_avg_invest_price = $inverstment_rlst["family_split_up"][$short_name]['stock'];
}
$inverstment_rlst["family_split_up"][$short_name]['stock'] = (int)$stock_avg_invest_price + (int)$avg_invest_price;
$networth = 0;
if($inverstment_rlst["family_networth"][$short_name]['networth']){
$networth = $inverstment_rlst["family_networth"][$short_name]['networth'];
}
$inverstment_rlst["family_networth"][$short_name]['networth'] = (int)$networth + (int)$avg_invest_price;
$stock_total_val += $avg_invest_price;
}
$total_inverstment += (int)$stock_total_val;
$total_networth += (int)$stock_total_val;
$inverstment_rlst["inverstment_summary"][] = array("name"=>"Stock","as_on"=>"(as on $today)","value"=>$stock_total_val);
//OTHERS INFORMATION
$others_total_val = 0;
$total_inverstment += (int)$others_total_val;
$total_networth += (int)$others_total_val;
$inverstment_rlst["inverstment_summary"][] = array("name"=>"Others","as_on"=>"(as on $today)","value"=>$others_total_val);
$inverstment_rlst["inverstment_summary"][] = array("name"=>"Total","as_on"=>"","value"=>$total_inverstment);
$inverstment_rlst["family_networth"]['total_networth'] = $total_networth;
return $inverstment_rlst;
}
public function get_chart_view($chart_info){
$chart_type = (int)$chart_info['chart_type'];
$render_to = $chart_info['render_to'];
$title = $chart_info['title'];
$legend = $chart_info['legend'];
$chart_data = $chart_info['chart_data'];
$chart_data = json_encode($chart_data);
$chart_content = "";
if($chart_type === 1){
$chart_content = "Highcharts.chart('$render_to', {
title: {
text: ''
},
chart: {
type: 'pie',
margin: [0, 0, 0, 0],
spacingTop: 0,
spacingBottom: 0,
spacingLeft: 0,
spacingRight: 0,
options3d: {
enabled: true,
alpha: 45,
beta: 0
},
marginTop: -80,
},
legend: {
useHTML: true,
labelFormatter: function() {
return this.name + ':' + this.y + ' - ' +this.percentage.toFixed(2)+'%';
},
symbolRadius: 0,
symbolHeight: 10,
symbolWidth: 15,
itemStyle: {
fontSize:'8px',
fontWeight: 'normal',
},
},
plotOptions: {
pie: {
size:'60%',
depth: 35,
showInLegend: $legend,
dataLabels: {
enabled: false,
formatter: function() {
shortText = this.point.name;
if(this.point.name.length >= 7){
shortText = jQuery.trim(this.point.name).substring(0,6) + ':' + this.point.y;
}
return shortText;
}
},
}
},
series: [$chart_data]
});\n
save_chart('$render_to');\n";
}
if($chart_type === 2){
$chart_content = "Highcharts.chart('$render_to', {
chart: {
type: 'line',
spacingLeft: 0,
},
title: {
text: ''
},
xAxis: {
type: 'category',
labels: {
rotation: -45,
style: {
fontSize: '8px',
fontWeight: 'normal',
}
}
},
yAxis: {
min: 0,
title: {
text: '$title',
style: {
fontSize: '9px',
fontWeight: 'normal',
}
}
},
legend: {
enabled: $legend
},
series: [$chart_data]
});\n
save_chart('$render_to')\n;";
}
return $chart_content;
}
public function save_chart_image(){
$chart_image = $this->input->post('chart_image');
$chart_image = str_replace(' ', '+', $chart_image);
$chart_image = base64_decode($chart_image);
$report_name = str_replace(" ","_",strtolower($this->report_data[0]->report_name));
$file_name = uniqid().".png";
$file_path = "./download_pdf/$report_name/$file_name";
$im = imagecreatefromstring($chart_image);
if ($im !== false) {
header('Content-Type: image/png');
imagepng($im, $file_path);
imagedestroy($im);
echo json_encode(array('success' => true,'message'=>"Chart Successfully Saved",'file_path'=>$file_path));
}else{
echo json_encode(array('success' => false,'message'=>"Unable to process chart data"));
}
}
}
?>