File: //home/cafsindia/groups_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 $logged_user_role;
public $report_id;
public $report_name;
public $date_filter;
public $group_column;
public $sum_column;
public $sum_qry_column;
public $sub_tot_show;
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');
$this->logged_user_role = $this->session->userdata('logged_user_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_table_filter_info();
$this->get_base_query();
$this->total_sum_column();
}
//BASE QUERY CONSTRUCTIONS
public function get_base_query(){
$report_tab_query = 'select * from cw_report_setting where prime_report_setting_id = "'.$this->report_id.'" and trans_status = "1"';
$report_tab_data = $this->db->query("CALL sp_a_run ('SELECT','$report_tab_query')");
$report_tab_result = $report_tab_data->result();
$report_tab_data->next_result();
$base_query = "";
if($report_tab_result){
$report_setting_id = $report_tab_result[0]->prime_report_setting_id;
$report_name = $report_tab_result[0]->report_name;
$report_for = $report_tab_result[0]->report_for;
$col_per_row = $report_tab_result[0]->col_per_row;
$table_info = $report_tab_result[0]->table_info;
$table_column = $report_tab_result[0]->table_column;
$this->report_name = $report_name;
$prime_table = $report_tab_result[0]->table_info;
$table_count = explode(",",$table_info);
$tab_count = count($table_count);
$date_filter = $report_tab_result[0]->date_filter;
$this->date_filter = $date_filter;
$date_column = $report_tab_result[0]->date_column;
$this->date_column = $date_column;
$group_column = $report_tab_result[0]->group_column;
$this->group_column = $group_column;
$sub_tot_show = $report_tab_result[0]->sub_tot_show;
$this->sub_tot_show = $sub_tot_show;
if((int)$tab_count > 1){
//WHERE TABLE JOIN DATA
$table_query = 'select * from cw_report_table where trans_status = 1 and join_for = "'.$report_setting_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;
$join_module_name = str_replace("cw_","",$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{
$line_table_query = " $table_info";
}
}
//WHERE CONDITIONS SEARCH
$where_condition = "";
$table_search_query = 'select where_condition from cw_report_where where where_for_id = "'.$this->report_id.'" and query_for = "'.$this->logged_user_role.'" and trans_status = "1"';
//echo $table_search_query; die;
$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_date_list = array("logged_DMY"=>"d-m-Y","logged_YMD"=>"Y-m-d","logged_MY"=>"m-Y","logged_YM"=>"Y-m","logged_Y"=>"Y");
$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."@";
if($session_date_list[$session_value]){
$date_formate = $session_date_list[$session_value];
$saved_session_val = date($date_formate);
}else{
$saved_session_val = $this->session->userdata($session_value);
}
$where_condition = str_replace($exist_val,$saved_session_val,$where_condition);
}
}
$this->table_search_info = $where_condition;
}
$base_query = "select @SELECT from $line_table_query";
$this->base_query = $base_query;
$this->prime_table = $prime_table;
}
public function get_table_filter_info(){
$table_qry = 'select module_column,table_column from cw_report_table_view where trans_status = 1 and report_id="'.$this->report_id.'" order by table_sort asc';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_qry')");
$result = $table_info->result();
$table_info->next_result();
$table_array = array();
$emp_export_arr = array();
foreach($result as $rslt){
$colum_name = "cw_".$rslt->module_column.".".$rslt->table_column;
$pattern = '/^cw_([a-z]+)\.\b/';
$replacement = '';
$label_list = preg_replace($pattern, $replacement, $colum_name);
$colum_name = explode(".",$colum_name);
$module_name = $colum_name[0];
$module_id = str_replace("cw_","",$module_name);
$label_name = $colum_name[1];
if($module_id === "transactions"){
$module_id = "employees";
$find_label_qry = 'select * from cw_form_setting where trans_status = 1 and prime_module_id = "'.$module_id.'" and label_name = "'.$label_name.'"';
$find_label_info = $this->db->query("CALL sp_a_run ('SELECT','$find_label_qry')");
$label_result = $find_label_info->result();
$find_label_info->next_result();
$label_id = $label_result[0]->label_name;
$field_type = (int)$label_result[0]->field_type;
$label_name = ucwords($label_result[0]->view_name);
$qry_label_name = ucwords(str_replace(" ","_",$label_name));
$this->select_query .= "cw_transactions.$label_id,";
$this->report_select_query .= "cw_transactions.$label_id as $qry_label_name,";
$table_array[] = array('field_type'=>$field_type,'label_name'=>$label_id,'view_name'=>$label_name);
}else
if($module_id === "monthly_input"){
$module_id = "employees";
$find_label_qry = 'select * from cw_form_setting where trans_status = 1 and prime_module_id = "'.$module_id.'" and label_name = "'.$label_name.'"';
$find_label_info = $this->db->query("CALL sp_a_run ('SELECT','$find_label_qry')");
$label_result = $find_label_info->result();
$find_label_info->next_result();
$label_id = $label_result[0]->label_name;
$field_type = (int)$label_result[0]->field_type;
$label_name = ucwords($label_result[0]->view_name);
$qry_label_name = ucwords(str_replace(" ","_",$label_name));
$this->select_query .= "cw_monthly_input.$label_id,";
$this->report_select_query .= "cw_monthly_input.$label_id as $qry_label_name,";
$table_array[] = array('field_type'=>$field_type,'label_name'=>$label_id,'view_name'=>$label_name);
}else{
$find_label_qry = 'select * from cw_form_setting where trans_status = 1 and prime_module_id = "'.$module_id.'" and label_name = "'.$label_name.'"';
$find_label_info = $this->db->query("CALL sp_a_run ('SELECT','$find_label_qry')");
$label_result = $find_label_info->result();
$find_label_info->next_result();
$prime_form_id = (int)$label_result[0]->prime_form_id;
$prime_module_id = $label_result[0]->prime_module_id;
$input_view_type = (int)$label_result[0]->input_view_type;
$input_for = (int)$label_result[0]->input_for;
$field_type = (int)$label_result[0]->field_type;
$label_id = $label_result[0]->label_name;
$label_name = ucwords($label_result[0]->view_name);
$field_length = $label_result[0]->field_length;
$field_decimals = $label_result[0]->field_decimals;
$pick_list_type = (int)$label_result[0]->pick_list_type;
$pick_list = $label_result[0]->pick_list;
$pick_table = $label_result[0]->pick_table;
$auto_prime_id = $label_result[0]->auto_prime_id;
$auto_dispaly_value = $label_result[0]->auto_dispaly_value;
$field_isdefault = (int)$label_result[0]->field_isdefault;
$table_name = "cw_".$prime_module_id;
if($this->report_id === "3" && $label_id === "stop_pay_status"){
$table_name = "cw_transactions";
}
if($field_isdefault === 1){
$pick_sel_table = "$table_name";
}
if((int)$prime_form_id === 0){
$label_name = ucwords(str_replace("_"," ",$label_list));
$label_id = $label_list;
}
$qry_label_name = ucwords(str_replace(" ","_",$label_name));
//TABLE HEADER
$table_array[] = array('field_type'=>$field_type,'label_name'=>$label_id,'view_name'=>$label_name);
$array_list = array();
//SEARCH FILTERS
if($field_type === 4){
$this->select_query .= "DATE_FORMAT($pick_sel_table.$label_id, '%d-%m-%Y') as $label_id,";
$this->report_select_query .= "DATE_FORMAT($pick_sel_table.$label_id, '%d-%m-%Y') as $qry_label_name,";
}else
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->report_select_query .= "$pick_query_as.$pick_list_val_2 as $qry_label_name,";
$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->report_select_query .= "$pick_query_as.$pick_list_val_2 as $qry_label_name,";
$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($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->report_select_query .= "$pick_query_as.$auto_dispaly_value as $qry_label_name,";
$this->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){
$this->select_query .= "$pick_sel_table.$label_id,";
$this->report_select_query .= "$pick_sel_table.$label_id as $qry_label_name,";
}else
if($field_isdefault === 2){
$this->select_query .= "$pick_sel_table.$label_id,";
$this->report_select_query .= "$pick_sel_table.$label_id as $qry_label_name,";
}
}
}
if((int)$prime_form_id !== 0){
$this->fliter_list[] = array('label_id'=> $label_id,'label_name'=> $label_name, 'field_isdefault'=> $field_isdefault, 'array_list'=> $array_list, 'field_type'=> $field_type);
}
$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);
$emp_export_arr[$label_id] = $label_name;
}
}
//get new column search value select query (add two column list values)
$add_column_qry = 'select * from cw_report_add_column where trans_status = 1 and report_id = "'.$this->report_id.'"';
$add_column_info = $this->db->query("CALL sp_a_run ('SELECT','$add_column_qry')");
$add_column_result = $add_column_info->result();
$add_column_info->next_result();
$select_qry = "";
if(!empty($add_column_result)){
foreach($add_column_result as $result){
$select_qry .= $result->select_condition;
$select_qry = str_replace("@","",$select_qry);
}
$select_qry = ltrim($select_qry,',');
$this->select_query .= $select_qry;
$this->report_select_query .= $select_qry;
}
$this->select_query = rtrim($this->select_query,',');
$this->report_select_query = rtrim($this->report_select_query,',');
$this->emp_export_arr = $emp_export_arr;
$this->form_info = json_decode(json_encode($this->form_info));
$this->table_info = json_decode(json_encode($table_array));
}
//get column wise total record
public function total_sum_column(){
$sum_column_qry = 'select * from cw_report_tot_column where trans_status = 1 and report_id = "'.$this->report_id.'"';
$sum_column_info = $this->db->query("CALL sp_a_run ('SELECT','$sum_column_qry')");
$sum_column_result = $sum_column_info->result();
$sum_column_info->next_result();
$sum_column = $sum_column_result[0]->sum_column_name;
$sum_column = explode(",",$sum_column);
$sum_column_val ="";
foreach($sum_column as $sum_info){
if(strpos($sum_info, 'cw_') !== false) {
$split = explode(".",$sum_info);
$this->sum_qry_column .= "sum($sum_info) as $split[1],";
$this->sum_column .= $split[1].",";
}else{
$add_column_qry = 'select * from cw_report_add_column where trans_status = 1 and report_id = "'.$this->report_id.'" and add_name = "'.$sum_info.'"';
$add_column_info = $this->db->query("CALL sp_a_run ('SELECT','$add_column_qry')");
$add_column_result = $add_column_info->result();
$add_column_count = (int)$add_column_info->num_rows();
$add_column_info->next_result();
if($add_column_count === 1){
$select_condition = $add_column_result[0]->select_condition;
$add_name = $add_column_result[0]->add_name;
$select_condition = ltrim(str_replace("@","",$select_condition),',');
$this->sum_qry_column .= $select_condition.",";
$this->sum_column .= $sum_info.",";
}
}
}
$this->sum_column = rtrim($this->sum_column,',');
$this->sum_qry_column = rtrim($this->sum_qry_column,',');
}
}
?>