File: /home/cafsindia/uds.cafsinfotech.in/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","5000M");
ini_set("max_execution_time","-1");
abstract class Report_controller extends Secure_Controller{
public $control_name;
public $logged_id;
public $logged_role;
public $logged_user_role;
public $logged_area_access;
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 $module_sts;
public $join_qry;
public $alias_control = array("cw_transactions_fms_man"=>"cw_employees","cw_transactions_fms"=>"cw_employees","cw_monthly_input_fms"=>"cw_employees","cw_pay_structure"=>"cw_employees"); //FOR FILTER PURPOSE!
public function __construct($module_id = NULL){
parent::__construct($module_id);
$this->module_sts = true;
$this->db_conn_id = $this->db->conn_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->logged_area_access = $this->session->userdata('logged_area_access');
}
//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";
}
if($line_prime_table === 'cw_employees' && $line_join_table === 'cw_pay_structure'){
$line_table_query .= " and cw_employees.position = cw_pay_structure.position ";
}
}
}else{
$line_table_query = " $table_info";
}
}
$base_query = "select @SELECT from $line_table_query";
$this->base_query = $base_query;
$this->prime_table = $prime_table;
//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];
$module_id_arr = array("transactions","transactions_fms","monthly_input","monthly_input_fms","transactions_fms_man","misc_input_fms",);
// ,"pre_audit_salary"
if (in_array($module_id, $module_id_arr)){
// if($module_id === "transactions" || $module_id === "transactions_fms" || $module_id === "monthly_input" || $module_id === "monthly_input_fms" || $module_id === "transactions_fms_man" || $module_id === "misc_input_fms"){
if($module_id === "transactions"){
$tab_name = "cw_transactions";
}
if($module_id === "transactions_fms"){
$tab_name = "cw_transactions_fms";
}
if($module_id === "monthly_input"){
$tab_name = "cw_monthly_input";
}
if($module_id === "monthly_input_fms"){
$tab_name = "cw_monthly_input_fms";
}
if($module_id === "transactions_fms_man"){
$tab_name = "cw_transactions_fms_man";
}
if($module_id === "misc_input_fms"){
$tab_name = "cw_misc_input_fms";
}
/*if($module_id === "pre_audit_salary"){
$tab_name = "cw_pre_audit_salary";
}*/
//ONLY FOR MISC INPUT FMS
if($module_id === "misc_input_fms"){
$module_id = 'employees","misc_input_fms';
// ,"misc_payment
}else{
$module_id = "employees";
}
$find_label_qry = 'select * from cw_form_setting where trans_status = 1 and prime_module_id in ("'.$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 in ("'.$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;
$mandatory_field = $label_result[0]->mandatory_field;
$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;
$search_show = (int)$label_result[0]->search_show;
$table_name = "cw_".$prime_module_id;
if($rslt->module_column === $pick_table){
$pick_sel_table = "$table_name";
}else{
$pick_sel_table = "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((int)$mandatory_field === 1){
$query_join_type = " inner join ";
}else{
$query_join_type = " left join ";
}
$array_list = array();
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,$pick_list_val_2";
}else{
$pick_select = $pick_list;
}
/*if($pick_table === "cw_category"){
$pick_query = "select $pick_select 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($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 .= " ".$query_join_type." $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 .= " ".$query_join_type." $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 .= " ".$query_join_type." $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, '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 = "";
}
// $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_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,";
}
if((int)$prime_form_id !== 0 && $search_show === 1){
$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($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;
$mandatory_field = $label_result[0]->mandatory_field;
$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;
$search_show = (int)$label_result[0]->search_show;
$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;
}
if((int)$mandatory_field === 1){
$query_join_type = " inner join ";
}else{
$query_join_type = " left join ";
}
//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_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,";
}
$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,$pick_list_val_2";
}else{
$pick_select = $pick_list;
}
// if($pick_table === "cw_category"){
// $pick_query = "select $pick_select 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($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 .= " ".$query_join_type." $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 .= " ".$query_join_type." $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $pick_sel_table.$label_id ";
}
}
}else
if($field_type === 9){
// if($rslt->table_column === "wbs_element"){
// $query_join_type = " inner join ";
// }else{
// $query_join_type = " left join ";
// }
$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 .= " ".$query_join_type." $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('.$pick_sel_table.'.'.$label_id.' != "0000-00-00 00:00:00", date_format('.$pick_sel_table.'.'.$label_id.', "%H:%i") , "") as '.$label_id.',';
$this->select_sum_query .= 'if('.$pick_sel_table.'.'.$label_id.' != "0000-00-00 00:00:00", date_format('.$pick_sel_table.'.'.$label_id.', "%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","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 && $search_show === 1){
$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, '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){
$select_qry .= $result->select_condition;
$select_qry = str_replace("@","",$select_qry);
$select_qry = str_replace('~','"',$select_qry);
}
$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,',');
}
/* BSK - Dynamic Report Modification START */
public function run_multi_qry($qry){
/* 1. NEED TO VALID is array */
$sts = false;
$msg = "no data processed";
$data = [];
$final_qry = implode(';',array_column($qry ?? [],'qry') ?? []).";";
if(mysqli_multi_query($this->db_conn_id, $final_qry)){
$sts = true;
$msg = "Qry processed";
$i = 0;
do{
if($result = mysqli_store_result($this->db_conn_id)){
while($row = mysqli_fetch_assoc($result)){
$data[$i][] = $row;
}
mysqli_free_result($result);
}else{
$data[$i] = [];
}
$qry[$i]["rslt"] = json_decode(json_encode($data[$i]));
$i++;
}
while(mysqli_more_results($this->db->conn_id) && mysqli_next_result($this->db->conn_id));
}
$final_rslt = [];
if($sts){
foreach ($qry as $key => $value) {
$final_rslt[$value['return']] = $value['rslt'];
}
}
return json_decode(json_encode(array("sts"=>$sts,"msg"=>$msg,"rslt"=>$final_rslt)));
}
public function page_info($report_id){
$table_info_qry = 'select * from cw_report_setting where trans_status = 1 and prime_report_setting_id = "'.$report_id.'"';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_info_qry')");
$table_info_result = $table_info->result();
$table_info->next_result();
$table_names = str_replace(',','","',$table_info_result[0]->table_info);
$table_names = str_replace('cw_','',$table_names);
$table_names = str_replace('monthly_input_fms','employees',$table_names);
$table_names = str_replace('transactions_fms','employees',$table_names);
$table_names = str_replace('transactions_fms_man','employees',$table_names);
$table_filters = explode(",",$table_info_result[0]->table_filter);
$pro_qry = [];
$pro_qry[] = array("return"=>"form_info","qry"=>'select prime_form_id,mandatory_field,label_name,field_type,table_show,search_show,field_isdefault,pick_list,pick_table,pick_list_type,auto_prime_id,auto_dispaly_value,input_view_type,pick_display_value,default_value,view_name,prime_module_id from cw_form_setting inner join cw_report_table_view on cw_report_table_view.table_column = cw_form_setting.label_name and prime_module_id in ("'.$table_names.'") where report_id = "'.$report_id.'" and cw_report_table_view.trans_status = "1" GROUP by cw_report_table_view.prime_report_table_view_id order by cw_report_table_view.table_sort');
$pro_qry[] = array("return"=>"company_info","qry"=>'select * from cw_company_information where cw_company_information.trans_status = 1');
$form_arr = [];
$form_set = 'select label_name,transaction_type from cw_form_setting where trans_status = 1';
$form_info = $this->db->query("CALL sp_a_run ('SELECT','$form_set')");
$form_result = $form_info->result_array();
$form_info->next_result();
foreach($form_result as $val){
$label_name = $val['label_name'];
$form_arr[$label_name] = $val;
}
if(count($pro_qry ?? []) > 0){
$page_info_rslt = $this->run_multi_qry($pro_qry);
if(!$page_info_rslt->sts){
$this->module_sts = false;
}else{
$condition_rslt = [];
$condition_list = json_decode(json_encode($page_info_rslt->rslt->condition_list),true);
if(count($condition_list ?? []) > 0){
$condition_key = array_column($condition_list ?? [], 'query_list_id');
$condition_val = array_column($condition_list ?? [], 'pick_where_condition');
$condition_rslt = array_combine($condition_key ?? [], $condition_val ?? []);
}
$filter_array = array();
foreach($table_filters as $values){
//Assign first part as key and second part as value
$seperate_arr = explode(".", $values);
$filter_array[$seperate_arr[1]] = $seperate_arr[0] ;
}
$table_info = [];
$filter_info = [];
$pro_pick_list = [];
foreach($page_info_rslt->rslt->form_info as $key => $value){
$field_type = (int)$value->field_type;
$input_view_type = (int)$value->input_view_type;
$label_name = $value->label_name;
$view_name = $value->view_name;
$pick_list = $value->pick_list;
$pick_table = $value->pick_table;
$auto_prime_id = $value->auto_prime_id;
$auto_display_value = $value->auto_display_value;
$input_name = "cw_".$value->prime_module_id;
//RENAME ->TRANSACTION,MONTHLY INPUT TABLE NAME AS EMPLOYEES!
if($label_name){
$tbl_name = $filter_array[$label_name];
$trans_type = $form_arr[$label_name]['transaction_type'];
if($filter_array[$label_name]){
$filter_info[$key] = array(
'field_type' => $field_type,
'label_name' => $label_name,
'view_name' => $view_name,
'tbl_name' => $tbl_name,
'pick_list' => $pick_list,
'pick_table' => $pick_table,
'auto_prime_id' => $auto_prime_id,
'auto_display_value' => $auto_display_value,
);
}
}
//FOE DEFAULT MODULE
if($input_view_type !== 3){
$table_info[$key] = $value;
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$pro_pick_list[$key] = $value;
}
}
}
$this->form_info = $page_info_rslt->rslt->form_info;
$this->table_head = $table_info;
$this->filter_info = $filter_info;
$this->company_info = $page_info_rslt->rslt->company_info;
if(count($pro_pick_list ?? []) > 0){
$this->get_pick_list_qry($pro_pick_list);
}
}
}
}
public function search_info($report_id){
$table_info_qry = 'select * from cw_report_setting where trans_status = 1 and prime_report_setting_id = "'.$report_id.'"';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_info_qry')");
$table_info_result = $table_info->result();
$table_info->next_result();
$report_tbls = $table_info_result[0]->table_info;
$table_det = explode(",",$report_tbls);
$table_names = str_replace(',','","',$table_info_result[0]->table_info);
$table_names = str_replace('cw_','',$table_names);
$table_names = str_replace('monthly_input_fms','employees',$table_names);
$table_names = str_replace('transactions_fms','employees',$table_names);
$table_names = str_replace('transactions_fms_man','employees',$table_names);
$this->date_filter = $table_info_result[0]->date_filter;
$this->date_column = $table_info_result[0]->date_column;
// UDY TEMP NNED TO REMOVE
$pro_qry = [];
$pro_qry[] = array("return"=>"form_info","qry"=>'select * from cw_form_setting inner join cw_report_table_view on cw_report_table_view.table_column = cw_form_setting.label_name and prime_module_id in ("'.$table_names.'") where report_id = "'.$report_id.'" and cw_report_table_view.trans_status = "1" order by cw_report_table_view.table_sort');
$pro_qry[] = array("return"=>"role_condition_list","qry"=>'select where_condition from cw_report_where where where_for_id = "'.$report_id.'" and FIND_IN_SET("'.$this->logged_user_role.'",query_for) and trans_status = "1"');
$pro_qry[] = array("return"=>"session_list","qry"=>'select session_value from cw_session_value where session_for = 1 and trans_status = "1" and session_value != "access_data"');
$pro_qry[] = array("return"=>"join_list","qry"=>'select * from cw_report_table where trans_status = 1 and join_for = "'.$report_id.'" ORDER BY abs(line_sort) asc');
if(count($pro_qry ?? []) > 0){
$search_info_rslt = $this->run_multi_qry($pro_qry);
if(!$search_info_rslt->sts){
$this->module_sts = false;
}else{
$this->form_info = $search_info_rslt->rslt->form_info;
$this->session_list = $search_info_rslt->rslt->session_list;
//BASIC SEARCH TABLE ROLE BASE CONDITION START
$role_condition_list = $search_info_rslt->rslt->role_condition_list[0];
$join_list = $search_info_rslt->rslt->join_list;
$where_condition = "";
if($role_condition_list->where_condition){
$where_condition = $role_condition_list->where_condition;
$where_condition = str_replace('^','"',$where_condition);
foreach($this->session_list as $session_val){
$session_value = $session_val->session_value;
$saved_session_val = $this->session->userdata($session_value);
$exist_val = "@".$session_value."@";
$where_condition = str_replace($exist_val,$saved_session_val,$where_condition);
$where_condition = str_replace(',','","',$where_condition);
}
}
$this->role_condition = $where_condition;
//BASIC SEARCH TABLE ROLE BASE CONDITION END
//Table Join List START
$this->join_qry = "";
if(count($table_det ?? []) > 1){
if($join_list){
foreach($join_list 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){
$this->prime_table = $prime_table;
$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";
}
if($line_prime_table === 'cw_employees' && $line_join_table === 'cw_pay_structure'){
$line_table_query .= " and cw_employees.position = cw_pay_structure.position ";
}
}
}
}else{
$this->prime_table = $report_tbls;
$line_table_query = " $report_tbls";
}
$base_query = "select @SELECT from $line_table_query";
$this->base_query = $base_query;
//Table Join List END
$table_info = [];
$search_arr = [];
$pro_pick_list = [];
$this->select_query = "";
//FOR SEARCH DATATABLE START
foreach($this->form_info as $key => $value){
$table_show = (int)$value->table_show;
$search_show = (int)$value->search_show;
$field_type = (int)$value->field_type;
$input_view_type = (int)$value->input_view_type;
$label_name = $value->label_name;
$module_column = $value->module_column;
$prime_form_id = (int)$value->prime_form_id;
$pick_list_type = (int)$value->pick_list_type;
$field_isdefault = (int)$value->field_isdefault;
$view_name = $value->view_name;
$pick_list = $value->pick_list;
$pick_table = $value->pick_table;
$auto_prime_id = $value->auto_prime_id;
$auto_display_value = $value->auto_display_value;
$input_name = "cw_".$value->prime_module_id;
//RENAME ->TRANSACTION,MONTHLY INPUT TABLE NAME AS EMPLOYEES!
$tbl_name = $input_name;
if($this->alias_control[$tbl_name]){
$tbl_name = $this->alias_control[$tbl_name];
}
if($tbl_name === $input_name){
$search_arr[$key] = array(
'prime_form_id' => $prime_form_id,
'field_type' => $field_type,
'pick_list' => $pick_list,
'pick_table' => $pick_table,
'pick_list_type' => $pick_list_type,
'input_view_type' => $input_view_type,
'auto_prime_id' => $auto_prime_id,
'auto_display_value' => $auto_display_value,
'label_name' => $label_name,
'field_isdefault' => $field_isdefault,
'tbl_name' => $tbl_name
);
}
//FOR SEARCH DATATABLE END
if($input_view_type !== 3){
$table_info[$key] = "cw_".$module_column.".".$label_name." as $label_name";
}
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$pro_pick_list[$key] = $value;
}
if($search_show === 1){
$filter_info[$key] = $value;
}
}
$table_head = $table_info;
$this->search_info_arr = $search_arr;
$this->select_query .= implode(",",$table_head ?? []);
//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;
}
}
}
}
public function get_pick_list_qry($pro_pick_list){
$pro_qry = [];
foreach($pro_pick_list as $setting){
$prime_form_id = (int)$setting->prime_form_id;
$input_view_type = (int)$setting->input_view_type;
$field_type = (int)$setting->field_type;
$label_id = $setting->label_name;
$label_name = ucwords($setting->view_name);
$pick_list_type = (int)$setting->pick_list_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$field_isdefault = (int)$setting->field_isdefault;
$mandatory_field = (int)$setting->mandatory_field;
$table_show = (int)$setting->table_show;
$search_show = (int)$setting->search_show;
$default_value = (int)$setting->default_value;
$pick_display = $setting->pick_display_value;
if($default_value === 0){
$default_value = "";
}
$pick_drop = array();
$pick_master = array();
$pick_key = array();
$pick_val = array();
$final_pick = array();
$where_condition = "";
//ROLE BASE CONDITION BUILD
if($this->condition_list[$prime_form_id]){
$where_condition = $this->condition_list[$prime_form_id];
$where_condition = str_replace('^','"',$where_condition);
foreach($this->session_list as $session_val){
$session_value = $session_val->session_value;
$saved_session_val = $this->session->userdata($session_value);
$exist_val = "@".$session_value."@";
$where_condition = str_replace($exist_val,$saved_session_val,$where_condition);
}
};
//echo "BSK $pick_list :: $pick_table :: $field_isdefault <br/>";
if($field_isdefault === 1){ // UDY NEED CHECK
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];
$imp_pick_list_val_1 = $pick_list_val_1;
$imp_pick_list_val_2 = $pick_list_val_2;
if($pick_display){
$pick_list = "$pick_list_val_1,$pick_list_val_2";
$pick_qry = 'select '.$pick_list.' from '.$pick_table.' where trans_status = 1 '.$where_condition;
if($pick_table === "cw_category"){
$pick_qry = 'select '.$pick_list.' from '.$pick_table.' where trans_status = 1 and prime_category_id != 1 '.$where_condition;
}else
/* Only for UDS */
$area_control_where = '';
if((int)$this->logged_user_role !== 1){
if($pick_table === "cw_sap_personal_area"){
$area_control_where = 'and '.$pick_table.'.personal_code in ('.$this->logged_area_access.') ';
}else{
$area_control_where = 'and cw_pay_structure.personal_code in ('.$this->logged_area_access.') ';
}
}
if($pick_table === "cw_sap_personal_area"){
$pick_qry = 'select '.$pick_list.' from '.$pick_table.' where '.$pick_table.'.trans_status = 1 '.$area_control_where.$where_condition;
}else
if($pick_table === "cw_sap_wbs"){
$qry = "wbs_element";
if($label_id === 'network_id'){
$qry = "network_id";
}
$pick_qry = 'select '.$pick_list.' from '.$pick_table.' inner join cw_pay_structure on cw_pay_structure.'.$qry.' = '.$pick_table.'.'.$pick_list_val_1.' where '.$pick_table.'.trans_status = 1 '.$area_control_where.$where_condition;
}else
if($pick_table === "cw_sap_activity"){
$pick_qry = 'select '.$pick_list.' from '.$pick_table.' inner join cw_pay_structure on cw_pay_structure.activity_no = '.$pick_table.'.'.$pick_list_val_1.' where '.$pick_table.'.trans_status = 1 '.$area_control_where.$where_condition;
}else
if($pick_table === "cw_sap_position"){
$pick_qry = 'select '.$pick_list.' from '.$pick_table.' inner join cw_pay_structure on cw_pay_structure.position = '.$pick_table.'.'.$pick_list_val_1.' where '.$pick_table.'.trans_status = 1 '.$area_control_where.$where_condition;
}else{
$pick_qry = 'select '.$pick_list.' from '.$pick_table.' where trans_status = 1 '.$where_condition;
}
$pick_qry = rtrim($pick_qry," ");
$pro_qry[] = array("return"=>$label_id,"qry"=>$pick_qry,"pro_info"=>array('prime_form_id' => $prime_form_id,'label_id' => $label_id,'label_name' => $label_name,'field_type' => $field_type,'pick_list_type' => $pick_list_type,'pick_list_val_1' => $pick_list_val_1,'pick_list_val_2' => $pick_list_val_2,'table_show' => $table_show,'search_show' => $search_show,'field_isdefault' => $field_isdefault,));
}
}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_qry = 'select '.$pick_list_val_1.','.$pick_list_val_2.' from '.$pick_table.' where '.$pick_list_val_3.' = 1'. $where_condition;
$pick_qry = rtrim($pick_qry," ");
$pro_qry[] = array("return"=>$label_id,"qry"=>$pick_qry,"pro_info"=>array('prime_form_id' => $prime_form_id,'label_id' => $label_id,'label_name' => $label_name,'field_type' => $field_type,'pick_list_type' => $pick_list_type,'pick_list_val_1' => $pick_list_val_1,'pick_list_val_2' => $pick_list_val_2,'table_show' => $table_show,'search_show' => $search_show,'field_isdefault' => $field_isdefault,));
}
}else
if($field_type === 9){
$pick_display_val = "CONCAT(".str_replace(",",'," ~ ",',$pick_display).") as auto_list_pick";
$pick_qry = 'select '.$auto_prime_id.','.$auto_dispaly_value.','.$pick_display_val .' from '. $pick_table .' where trans_status = 1';
$pick_qry = rtrim($pick_qry," ");
$pro_qry[] = array("return"=>$label_id,"qry"=>$pick_qry,"pro_info"=>array('prime_form_id' => $prime_form_id,'label_id' => $label_id,'label_name' => $label_name,'field_type' => $field_type,'auto_prime_id' => $auto_prime_id,'auto_dispaly_value' => $auto_dispaly_value,'table_show' => $table_show,'search_show' => $search_show,'field_isdefault' => $field_isdefault,));
}
}
}
if(count($pro_qry ?? []) > 0){
$pick_list_info = $this->run_multi_qry($pro_qry);
if(!$pick_list_info->sts){
$this->module_sts = false;
}else{
$pick_list_rslt = [];
$pick_list_rslt = json_decode(json_encode($pick_list_info->rslt),true);
foreach($pick_list_rslt as $pick_key=>$list_info){
$is_exist_pro_qry = array_search($pick_key ?? [], array_column($pro_qry, 'return'));
if($pro_qry[$is_exist_pro_qry]){
//pro info array format
$pro_info = $pro_qry[$is_exist_pro_qry]["pro_info"];
$prime_form_id = $pro_info['prime_form_id'];
$label_id = $pro_info['label_id'];
$label_name = $pro_info['label_name'];
$field_type = (int)$pro_info['field_type'];
$table_show = (int)$pro_info['table_show'];
$search_show = (int)$pro_info['search_show'];
$field_isdefault = (int)$pro_info['field_isdefault'];
if($field_type === 5 || $field_type === 7){
$pick_list_type = (int)$pro_info['pick_list_type'];
$pick_list_val_1 = $pro_info['pick_list_val_1'];
$pick_list_val_2 = $pro_info['pick_list_val_2'];
// if($pick_list_type === 1){
$pick_key = array_column($list_info ?? [], $pick_list_val_1);
$pick_val = array_column($list_info ?? [], $pick_list_val_2);
$final_pick = array_combine( $pick_key ?? [], $pick_val ?? []);
if($final_pick){
$final_pick = array("" => "---- $label_name ----") + $final_pick;
}
// }
}else
if($field_type === 9){
$auto_prime_id = $pro_info['auto_prime_id'];
$auto_dispaly_value = $pro_info['auto_dispaly_value'];
$pick_key = array_column($list_info ?? [], $auto_prime_id);
$pick_val = array_column($list_info ?? [], 'auto_list_pick');
$final_pick = array_combine( $pick_key ?? [], $pick_val ?? []);
}
$this->pick_list[$label_id] = array('label_id'=> $label_id, 'label_name'=> $label_name, 'field_isdefault'=> $field_isdefault, 'array_list'=> $final_pick, 'field_type'=> $field_type,'prime_form_id'=>$prime_form_id);
}
}
}
}
}
/* BSK - Dynamic Report Modification END */
}
?>