File: /home/cafsindia/hrms_allyindian_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");
ini_set("memory_limit","2048M");
ini_set("max_execution_time","60000");
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 $select_sum_query;
public $sub_tot_show;
public $total_tbl_count;
public $join_tbl_count;
public $add_info;
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');
$this->add_info = array();
}
//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_user_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;
$this->total_tbl_count = $tab_count-1;
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();
$this->join_tbl_count = count($table_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 FIND_IN_SET("'.$this->logged_user_role.'",query_for) 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);
$where_condition = str_replace('~','"',$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);
}
}
$where_condition = str_replace(',','","',$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,table_sort 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();
$get_add_col_qry = 'select module_column,REPLACE(add_name, " ", "_") as table_column,cw_report_table_view.table_sort from cw_report_add_column inner join cw_report_table_view on cw_report_table_view.table_column = cw_report_add_column.add_name where cw_report_add_column.report_id ="'.$this->report_id.'" and cw_report_table_view.trans_status = 1 and cw_report_add_column.trans_status = 1 group by prime_report_add_column_id order by abs(cw_report_table_view.table_sort)';
$get_add_col_info = $this->db->query("CALL sp_a_run ('SELECT','$get_add_col_qry')");
$get_add_col_rslt = $get_add_col_info->result();
$get_add_col_info->next_result();
$result = array_merge($result ?? [],$get_add_col_rslt ?? []);
array_multisort(array_column($result ?? [], 'table_sort'), SORT_ASC, $result);
$result = array_unique($result ?? [], SORT_REGULAR);
$table_array = 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 === "transactions_fms"){
if($module_id === "transactions"){
$tab_name = "cw_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();
if($label_result){
$label_id = $label_result[0]->label_name;
$field_type = (int)$label_result[0]->field_type;
$label_name = ucwords($label_result[0]->view_name);
// $this->select_query .= "$tab_name.$label_id,";
/*latha start select and join employee master based*/
$find_label_qry = 'select * from cw_form_setting where trans_status = 1 and prime_module_id = "'.$module_id.'" and label_name = "'.$label_id.'"';
$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;
$pick_display = $label_result[0]->pick_display_value;
$text_type = (int)$label_result[0]->text_type;
$date_type = (int)$label_result[0]->date_type;
$table_name = "cw_".$prime_module_id;
if(!$prime_module_id){
$table_name = "cw_".$rslt->module_column;
}
// 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;
}
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];
//FOR SHOW IN FILTER (BASED ON PICK DISPLAY VALUE)
if($pick_display){
$pick_select = "$pick_list_val_1,CONCAT_WS(\" - \", $pick_display) as $pick_list_val_2";
}else{
$pick_select = $pick_list;
}
/*if($pick_table === "cw_category"){
$pick_query = "select $pick_list from $pick_table where trans_status = 1 and prime_category_id != 1 $where_condition";
}else{*/
$pick_query = "select $pick_select from $pick_table where trans_status = 1 $where_condition";
/*}*/
$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."_trans";
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
if($pick_table === "cw_employees"){
$this->select_query .= 'CONCAT_WS(" - ", '.$pick_query_as.'.'.$pick_list_val_1.','.$pick_query_as.'.'.$pick_list_val_2.') as '.$label_id.',';
}else{
$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.'_trans';
$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($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 ";
}
}else{
$this->select_query .= "$tab_name.$label_id,";
}
/*latha end select and join employee master based*/
$report_tbl_name = "$tab_name";
$this->select_sum_query .= "SUM($tab_name.$label_id) as $label_id,";
//FORM INFO CONDITIONS
$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,'view_name'=>$label_name, 'field_isdefault'=> $field_isdefault,'pick_list_type'=>$pick_list_type);
}else{
$field_type = 1;
$label_id = strtolower(str_replace(' ', '_',$label_name));
$label_name = ucfirst(str_replace('_', ' ', $label_id));
$report_tbl_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,'view_name'=>$label_name, 'field_isdefault'=> $field_isdefault,'pick_list_type'=>$pick_list_type);
}
// $table_array[] = array('field_type'=>$field_type,'label_name'=>$label_id,'view_name'=>$label_name,'report_tbl_name'=>$report_tbl_name);
//TABLE HEADER
if($rslt->table_column === "trans_created_date" || $rslt->table_column === "trans_updated_date" || $rslt->table_column === "trans_deleted_date"){
$table_array[] = array('field_type'=>4,'text_type'=>'','date_type'=>1,'label_name'=>$rslt->table_column,'view_name'=>ucfirst(str_replace("_", " ", $rslt->table_column)),'report_tbl_name'=>$pick_sel_table);
}else
if($rslt->table_column === "trans_status" || $rslt->table_column === "trans_created_by" || $rslt->table_column === "trans_updated_by" || $rslt->table_column === "trans_deleted_by"){
$table_array[] = array('field_type'=>1,'text_type'=>'','date_type'=>'','label_name'=>$rslt->table_column,'view_name'=>ucfirst(str_replace("_", " ", $rslt->table_column)),'report_tbl_name'=>$pick_sel_table);
}else{
$table_array[] = array('field_type'=>$field_type,'text_type'=>$text_type,'date_type'=>$date_type,'label_name'=>$label_id,'view_name'=>$label_name,'report_tbl_name'=>$pick_sel_table);
}
// $array_list = array();
//SEARCH FILTERS
if($rslt->table_column === "trans_created_date" || $rslt->table_column === "trans_updated_date" || $rslt->table_column === "trans_deleted_date"){
$this->select_query .= 'IF('.$pick_sel_table.'.'.$rslt->table_column.',DATE_FORMAT('.$pick_sel_table.'.'.$rslt->table_column.', "%d-%m-%Y %H:%i:%s"),"") as '.$rslt->table_column.',';
}
if($rslt->table_column === "trans_status" || $rslt->table_column === "trans_created_by" || $rslt->table_column === "trans_updated_by" || $rslt->table_column === "trans_deleted_by"){
$this->select_query .= "$pick_sel_table.$rslt->table_column,";
}
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,'filter_tbl_val'=>$pick_sel_table);
}
}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);
$this->select_query .= "cw_monthly_input.$label_id,";
$this->select_sum_query .= "SUM(cw_monthly_input.$label_id) as $label_id,";
$table_array[] = array('field_type'=>$field_type,'label_name'=>$label_id,'view_name'=>$label_name,'report_tbl_name'=>'cw_monthly_input');
}else{
if($rslt->module_column === "custom_employees_log" || $rslt->module_column === "custom_employees"){
$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();
$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;
if($label_result){
$field_type = (int)$label_result[0]->field_type;
}else{
$field_type = 1;
}
$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;
$pick_display = $label_result[0]->pick_display_value;
$text_type = (int)$label_result[0]->text_type;
$date_type = (int)$label_result[0]->date_type;
$table_name = "cw_".$prime_module_id;
if(!$prime_module_id){
$table_name = "cw_".$rslt->module_column;
}
if($rslt->module_column === "custom_employees_log" || $rslt->module_column === "custom_employees"){
$table_name = "cw_".$rslt->module_column;
}
/*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;
}
//TABLE HEADER
if($rslt->table_column === "trans_created_date" || $rslt->table_column === "trans_updated_date" || $rslt->table_column === "trans_deleted_date"){
$table_array[] = array('field_type'=>4,'text_type'=>'','date_type'=>1,'label_name'=>$rslt->table_column,'view_name'=>ucfirst(str_replace("_", " ", $rslt->table_column)),'report_tbl_name'=>$pick_sel_table);
}else
if($rslt->table_column === "trans_created_by" || $rslt->table_column === "trans_updated_by" || $rslt->table_column === "trans_deleted_by"){
$table_array[] = array('field_type'=>1,'text_type'=>'','date_type'=>'','label_name'=>$rslt->table_column,'view_name'=>ucfirst(str_replace("_", " ", $rslt->table_column)),'report_tbl_name'=>$pick_sel_table);
}else{
$table_array[] = array('field_type'=>$field_type,'text_type'=>$text_type,'date_type'=>$date_type,'label_name'=>$label_id,'view_name'=>$label_name,'report_tbl_name'=>$pick_sel_table);
}
// $array_list = array();
//SEARCH FILTERS
if($rslt->table_column === "trans_created_date" || $rslt->table_column === "trans_updated_date" || $rslt->table_column === "trans_deleted_date"){
$this->select_query .= 'IF('.$pick_sel_table.'.'.$rslt->table_column.',DATE_FORMAT('.$pick_sel_table.'.'.$rslt->table_column.', "%d-%m-%Y %H:%i:%s"),"") as '.$rslt->table_column.',';
}
if($rslt->table_column === "trans_created_by" || $rslt->table_column === "trans_updated_by" || $rslt->table_column === "trans_deleted_by"){
$this->select_query .= "$pick_sel_table.$rslt->table_column,";
}
$array_list = array();
//SEARCH FILTERS
if($field_type === 4){
if($date_type === 1){
$this->select_query .= 'IF(('.$pick_sel_table.'.'.$label_id.' IS NOT NULL and '.$pick_sel_table.'.'.$label_id.' != "0000-00-00") ,DATE_FORMAT('.$pick_sel_table.'.'.$label_id.', "%d-%m-%Y"),"") as '.$label_id.',';
$this->select_sum_query .= 'IF('.$pick_sel_table.'.'.$label_id.',DATE_FORMAT('.$pick_sel_table.'.'.$label_id.', "%d-%m-%Y"),"") as '.$label_id.',';
}else{
$this->select_query .= "$pick_sel_table.$label_id,";
$this->select_sum_query .= "$pick_sel_table.$label_id,";
}
// DATE_FORMAT(str_to_date(CONCAT("01-"'.$pick_sel_table.'.'.$label_id.'), "%d-%m-%Y") , "%Y-%m")
}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];
//FOR SHOW IN FILTER (BASED ON PICK DISPLAY VALUE)
if($pick_display){
$pick_select = "$pick_list_val_1,CONCAT_WS(\" - \", $pick_display) as $pick_list_val_2";
}else{
$pick_select = $pick_list;
}
// if($pick_table === "cw_category"){
// $pick_query = "select $pick_list from $pick_table where trans_status = 1 and prime_category_id != 1 $where_condition";
// }else{
$pick_query = "select $pick_select from $pick_table where trans_status = 1 $where_condition";
// }
$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)){
if($pick_table === "cw_employees"){
$this->select_query .= 'CONCAT_WS(" - ", '.$pick_query_as.'.'.$pick_list_val_1.','.$pick_query_as.'.'.$pick_list_val_2.') as '.$label_id.',';
$this->select_sum_query .= 'CONCAT_WS(" - ", '.$pick_query_as.'.'.$pick_list_val_1.','.$pick_query_as.'.'.$pick_list_val_2.') as '.$label_id.',';
}else{
$this->select_query .= "$pick_query_as.$pick_list_val_2 as $label_id,";
$this->select_sum_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->select_sum_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($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->select_sum_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 ";
}
}else
if($field_type === 13){
$date_time_arr = array("punch_in","punch_out","permission_in","permission_out","manual_in","manual_out");
if(in_array($label_id, $date_time_arr)){
$this->select_query .=
'IF(NULLIF('.$pick_sel_table.'.'.$label_id.', "0000-00-00 00:00:00") IS NOT NULL,
DATE_FORMAT(NULLIF('.$pick_sel_table.'.'.$label_id.', "0000-00-00 00:00:00"), "%H:%i"),
""
) AS '.$label_id.',';
$this->select_sum_query .=
'IF(NULLIF('.$pick_sel_table.'.'.$label_id.', "0000-00-00 00:00:00") IS NOT NULL,
DATE_FORMAT(NULLIF('.$pick_sel_table.'.'.$label_id.', "0000-00-00 00:00:00"), "%H:%i"),
""
) AS '.$label_id.',';
}else{
$this->select_query .= 'DATE_FORMAT('.$pick_sel_table.'.'.$label_id.', "%d-%m-%Y %H:%i:%s") as '.$label_id.',';
$this->select_sum_query .= 'DATE_FORMAT('.$pick_sel_table.'.'.$label_id.', "%d-%m-%Y %H:%i:%s") as '.$label_id.',';
}
}else
if($field_type === 15){
$this->select_query .= 'DATE_FORMAT('.$pick_sel_table.'.'.$label_id.', "%H:%i") as '.$label_id.',';
}else{
if(($input_view_type === 1) || ($input_view_type === 2)){
$min_arr = array("early_in","late_in","early_out","excess_out","total_late_hours","total_work_hours","final_total_work_hours","approved_ot_mins","approved_shift_ot");
if($field_isdefault === 1){
if(in_array($label_id, $min_arr)){
$this->select_query .= 'if('.$pick_sel_table.'.'.$label_id.' > 0, time_format(concat(floor('.$pick_sel_table.'.'.$label_id.'/60),":",lpad(mod('.$pick_sel_table.'.'.$label_id.',60),2,"0")),"%H:%i"), '.$pick_sel_table.'.'.$label_id.') as '.$label_id.',';
$this->select_sum_query .= 'if('.$pick_sel_table.'.'.$label_id.' > 0, time_format(concat(floor('.$pick_sel_table.'.'.$label_id.'/60),":",lpad(mod('.$pick_sel_table.'.'.$label_id.',60),2,"0")),"%H:%i"), '.$pick_sel_table.'.'.$label_id.') as '.$label_id.',';
}else{
$this->select_query .= "$pick_sel_table.$label_id,";
$this->select_sum_query .= "$pick_sel_table.$label_id,";
}
}else
if($field_isdefault === 2){
$this->select_query .= "$pick_sel_table.$label_id,";
$this->select_sum_query .= "$pick_sel_table.$label_id,";
}
}
}
if($rslt->module_column === "custom_employees"){
$prime_module_id = "custom_employees";
}
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,'filter_tbl_val'=>$pick_sel_table);
}
$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,'view_name'=>$label_name, 'field_isdefault'=> $field_isdefault,'pick_list_type'=>$pick_list_type);
}
}
//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){
$add_name = $result->add_name;
$select_qry .= $result->select_condition;
$select_qry = str_replace("@","",$select_qry);
$select_qry = str_replace('~','"',$select_qry);
$this->add_info[$add_name] = ltrim($result->select_condition,',');
}
$select_qry = ltrim($select_qry,',');
$this->select_query .= $select_qry;
$this->select_sum_query .= $select_qry;
}
$this->select_query = rtrim($this->select_query,',');
$this->select_sum_query = rtrim($this->select_sum_query,',');
$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,',');
}
// Get Default Leave Financial Year
public function get_leave_financial_details(){
$fin_query = 'SELECT prime_leave_financial_year_id,starting_date,ending_date from cw_leave_financial_year where set_as_default_financial_year = 1 and trans_status = "1"';
$fin_data = $this->db->query("CALL sp_a_run ('SELECT','$fin_query')");
$fin_result = $fin_data->result();
$fin_data->next_result();
return $fin_result;
}
// //Generate Key
// public function generateKey(){
// $sess_id = $this->session->userdata('__ci_last_regenerate');
// $employee_code = $this->session->userdata('logged_emp_code');
// $encKey = $this->config->item("encKey");
// $combineKey = $sess_id."||".$employee_code."||".$encKey;
// $key = hash('sha512', $combineKey);
// return $key;
// }
// //Decrypt Encrypted string from Javascript
// public function cryptoDecrypt($encString){
// try{
// $key = $this->generateKey();
// $password = hash('sha512', $key);
// $keySize = 256;
// $iterations = 1000;
// $decSalt = substr($encString, 0, 32);
// $decIvhex = substr($encString, 32, 32);
// $encrypted = substr($encString, 64);
// $decKey = hash_pbkdf2( 'sha1', $password, hex2bin($decSalt), $iterations, $keySize / 8, true);
// // AES decryption using OpenSSL in PHP
// $decrypted = openssl_decrypt(
// base64_decode($encrypted), // Encrypted data (decode from Base64)
// 'AES-256-CBC', // Cipher method (AES-256-CBC for 256-bit key size)
// $decKey, // Decryption key
// OPENSSL_RAW_DATA, // Options: raw data output
// hex2bin($decIvhex) // Initialization vector
// );
// if(!$decrypted){
// throw new Exception('Error0001..');
// }else{
// return json_decode($decrypted,TRUE);
// }
// }catch(Exception $e){
// // Log the error or handle it as needed
// error_log("Decryption Error: " . $e->getMessage()); // Log the error for debugging
// return false;
// }
// }
}
?>