File: //home/cafsindia/cpaqua.cafsinfotech.in/application/controllers/Custom_base_controller.php
<?php
/**********************************************************
Filename: Custom action controller
Description: Custom Action Controller for custom approval data move to main emoloyee master controller.
Author: Jaffer
Created on: 28 JAN 2020
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 Custom_base_controller extends Secure_Controller{
public $control_name;
public $logged_id;
public $logged_role;
public $logged_user_role;
public $prime_id;
public $prime_table;
public $base_query = "" ;
public $logged_zone;
public $logged_site_location;
public $logged_site_name;
public $logged_cluster_name;
public $quick_link = array();
public $table_head = array();
public $view_info = array();
public $form_info = array();
public $rowset_form_info= array();
public $role_condition;
public $select_query = "";
public $view_select = "";
public $all_pick = array();
public $master_pick = array();
public $email_setting = array();
public $company_info = array();
public $role_based_condition = array();
public $formula_result = array();
private $db_conn_id;
public $module_sts;
public $module_id;
public $condition_list = array();
public $session_list = array();
public $pick_list = array();
public $depen_auto_list = array();
public $form_condition_list = array();
public $row_view_list = array();
public function __construct($module_id = NULL){
parent::__construct($module_id);
$this->module_sts = true;
$this->db_conn_id = $this->db->conn_id;
$this->module_id = $module_id; // UDY CHAG FOR $this->control_name
$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_emp_code = $this->session->userdata('logged_emp_code');
$this->logged_area_access = $this->session->userdata('logged_area_access');
if($this->control_name === "custom_approval"){
$this->control_name = "custom_employees";
}
$this->prime_id = "prime_".$this->control_name."_id";
$this->prime_table = $this->db->dbprefix($this->control_name);
$this->base_query = "select @SELECT@ from $this->prime_table";
$this->select_query = "$this->prime_table.$this->prime_id,";
$this->view_select = "$this->prime_table.$this->prime_id,";
}
/******************** UDY _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){
$final_rslt = array_reduce($qry, function($result, $arr){
$result[$arr['return']] = $arr['rslt'];
return $result;
}, array());
}
return json_decode(json_encode(array("sts"=>$sts,"msg"=>$msg,"rslt"=>$final_rslt)));
}
public function page_info(){
$control_whr_cond = "";
$control_join_cond = "";
if($this->control_name === "custom_employees"){
$this->control_name = 'employees';
$control_name = 'employees';
$control_whr_cond = ' and cw_form_setting.onboard_input = "1"';
$control_join_cond = ' inner join cw_form_setting on cw_form_setting.input_for = cw_form_view_setting.prime_form_view_id';
}
$order_by = ' ORDER BY input_for,field_sort asc';
$pro_qry = [];
$pro_qry[] = array("return"=>"quicklink","qry"=>'select quicklink from cw_modules where module_id = "'.$this->control_name.'"');
$pro_qry[] = array("return"=>"form_info","qry"=>'select * from cw_form_setting where prime_module_id = "'.$control_name.'" and field_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for)'.$control_whr_cond.$order_by);
$pro_qry[] = array("return"=>"freeze_list","qry"=>'select left_freeze,right_freeze from cw_modules where module_id = "'.$this->control_name.'" and trans_status = "1"');
$pro_qry[] = array("return"=>"condition_list","qry"=>'select query_list_id,pick_where_condition from cw_pick_base_search where pick_module_id = "'.$this->control_name.'" and pick_query_for = "'.$this->logged_user_role.'" 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"=>"company_info","qry"=>'select * from cw_company_information where cw_company_information.trans_status = 1');
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 ?? []);
}
$table_info = [];
$filter_info = [];
$pro_pick_list = [];
foreach($page_info_rslt->rslt->form_info as $key => $value){
$field_type = (int)$value->field_type;
$table_show = (int)$value->table_show;
$search_show = (int)$value->search_show;
$input_view_type = (int)$value->input_view_type;
//FOR MI AND MI FMS AND TIME SHEET
//FOE DEFAULT MODULE
if($table_show === 1){
if($input_view_type !== 3){
$table_info[$key] = $value;
}
}
if($search_show === 1){
$filter_info[$key] = $value;
}
if(($search_show === 1) || ($table_show === 1)){
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$pro_pick_list[$key] = $value;
}
}
}
array_multisort(array_column($table_info ?? [], 'table_sort'), SORT_ASC, $table_info);
$this->quick_link = $page_info_rslt->rslt->quicklink[0];
$this->form_info = $page_info_rslt->rslt->form_info;
$this->freeze_list = $page_info_rslt->rslt->freeze_list[0];
$this->table_head = $table_info;
$this->fliter_list = $filter_info;
$this->condition_list = $condition_rslt;
$this->session_list = $page_info_rslt->rslt->session_list;
$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(){
$control_whr_cond = "";
$control_join_cond = "";
if($this->control_name === "custom_employees"){
$this->control_name = 'employees';
$control_name = 'employees';
$control_whr_cond = ' and cw_form_setting.onboard_input = "1"';
$control_join_cond = ' inner join cw_form_setting on cw_form_setting.input_for = cw_form_view_setting.prime_form_view_id';
}
$order_by = ' ORDER BY input_for,field_sort asc';
$pro_qry = [];
$pro_qry[] = array("return"=>"form_info","qry"=>'select * from cw_form_setting where prime_module_id = "'.$control_name.'" and field_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for)'.$control_whr_cond.$order_by);
$pro_qry[] = array("return"=>"role_condition_list","qry"=>'select where_condition from cw_form_table_search where query_module_id = "custom_approval" and query_for = "'.$this->logged_user_role.'" 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"=>"company_info","qry"=>'select * from cw_company_information where cw_company_information.trans_status = 1');
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];
$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_info = [];
$filter_info = [];
$pro_pick_list = [];
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;
if($table_show === 1){
if($input_view_type !== 3){
$table_info[$key] = $value;
}
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$pro_pick_list[$key] = $value;
}
}
if($search_show === 1){
$filter_info[$key] = $value;
}
}
$this->table_head = $table_info;
$this->fliter_list = $filter_info;
$select_key = array_column($this->table_head ?? [], "label_name");
$this->select_query .= implode(",",$select_key ?? []);
//PICKLIST & AUTOCOMPLETE DATA
if(count($pro_pick_list ?? []) > 0){
$this->get_pick_list_qry($pro_pick_list);
}
}
}
}
public function view_info($prime_id){
$control_whr_cond = "";
$control_join_cond = "";
if($this->control_name === "custom_employees"){
$this->control_name = 'employees';
$control_name = 'employees';
$control_whr_cond = ' and cw_form_setting.onboard_input = "1"';
$control_join_cond = ' inner join cw_form_setting on cw_form_setting.input_for = cw_form_view_setting.prime_form_view_id';
}
$order_by = ' ORDER BY input_for,field_sort asc';
$pro_qry = [];
$pro_qry[] = array("return"=>"form_info","qry"=>'select * from cw_form_setting where prime_module_id = "'.$control_name.'" and field_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for)'.$control_whr_cond.$order_by);
$pro_qry[] = array("return"=>"view_info","qry"=>'select DISTINCT cw_form_view_setting.* from cw_form_view_setting '.$control_join_cond.' where prime_view_module_id = "'.$this->control_name.'" and cw_form_view_setting.form_view_show = "1" and cw_form_view_setting.trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",cw_form_view_setting.view_user_right)'.$control_whr_cond.' ORDER BY cw_form_view_setting.form_view_sort asc');
$pro_qry[] = array("return"=>"formula_result","qry"=>'select * from cw_form_bind_input where input_cond_module_id = "'.$this->control_name.'" and trans_status = 1');
//for read only option qry
$pro_qry[] = array("return"=>"role_based_condition","qry"=>'select * from cw_role_base_condition where role_module_id = "'.$this->control_name.'" and find_in_set("'.$this->logged_user_role.'",role_condition_for) and trans_status = 1');
$pro_qry[] = array("return"=>"condition_list","qry"=>'select query_list_id,pick_where_condition from cw_pick_base_search where pick_module_id = "'.$this->control_name.'" and pick_query_for = "'.$this->logged_user_role.'" 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"');
//DHR COMMAND FOR FORM CONDITION LIST (IT ADD FROM LAST)
$pro_qry[] = array("return"=>"form_condition_list","qry"=>'select * from cw_form_condition_formula where cond_module_id = "'.$this->control_name.'" and trans_status = "1" and FIND_IN_SET("'.$this->logged_role.'",condition_for)');
//Get Company Info
$pro_qry[] = array("return"=>"company_info","qry"=>'select * from cw_company_information where cw_company_information.trans_status = 1');
//DHR COMMAND FOR FORM CONDITION LIST (IT ADD FROM LAST)
if(count($pro_qry ?? []) > 0){
$view_info_rslt = $this->run_multi_qry($pro_qry);
if(!$view_info_rslt->sts){
$this->module_sts = false;
}else{
$condition_rslt = [];
$condition_list = json_decode(json_encode($view_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 ?? []);
}
//FOR ROLE BASE CONDITIONS
$role_based_condition = [];
$role_based_list = json_decode(json_encode($view_info_rslt->rslt->role_based_condition),true);
foreach($role_based_list as $key => $condition) {
$role_based_condition[$condition['user_condition_type']] = $condition['input_columns'];
}
$pro_pick_list = [];
$depen_auto_pick_list = [];
foreach($view_info_rslt->rslt->form_info as $key => $value){
$field_type = (int)$value->field_type;
$field_show = (int)$value->field_show;
$depen_pick_list = (int)$value->dependent_pick_list;
$label_id = $value->label_name;
$input_view_type = (int)$value->input_view_type;
if($field_show === 1){
if($input_view_type !== 3){
$this->view_select .= "$this->prime_table.$label_id,";
}
if($depen_pick_list === 2){
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$pro_pick_list[$key] = $value;
}
}else
if($depen_pick_list === 1 && ($field_type === 5 || $field_type === 7)){
$pro_pick_list[$key] = $value;
}else
if($depen_pick_list === 1 && $field_type === 9){
$depen_auto_pick_list[$key] = $value;
}
}
}
//VIEW INFO LIST
$this->view_info = $view_info_rslt->rslt->view_info;
$this->form_info = $view_info_rslt->rslt->form_info;
$this->formula_result = $view_info_rslt->rslt->formula_result;
$this->role_based_condition = $role_based_condition;
$this->condition_list = $condition_rslt;
$this->session_list = $view_info_rslt->rslt->session_list;
$this->company_info = $view_info_rslt->rslt->company_info;
//DHR COMMAND FOR FORM CONDITION LIST (IT ADD FROM LAST)
$form_condition_rslt = $view_info_rslt->rslt->form_condition_list;
if(count($form_condition_rslt ?? []) > 0){
$this->form_condition_list($form_condition_rslt);
}
//DHR COMMAND FOR FORM CONDITION LIST (IT ADD FROM LAST)
$this->view_select = rtrim($this->view_select,',');
// ONLY FOR EMPLOYEE
if($this->control_name === "employees"){
$this->view_select = $this->view_select .',user_name,password';
}
if(count($pro_pick_list ?? []) > 0){
$this->get_pick_list_qry($pro_pick_list);
}
if(count($depen_auto_pick_list ?? []) > 0){
if($prime_id){
$this->get_depen_auto_list_qry($depen_auto_pick_list,$prime_id);
}
}
//FOR ROW SET FORMAT START
$row_view_list = array_filter($this->view_info, function ($value) {
return (json_decode(json_encode((int)$value->form_view_type === 3)));
});
if(count($row_view_list ?? []) > 0){
$row_form_list = array_reduce($this->form_info, function($result, $arr){
if((int)$arr->input_view_type === 3 && (int)$arr->table_show === 1){
$result[$arr->input_for][] = $arr;
};
return $result;
}, array());
//THIS FUNCTION WORK PENDING SO PLEASE DON'T REMOVE IT
// $this->row_view_list = $this->get_row_set_list($row_view_list,$row_form_list,$prime_id);
//THIS FUNCTION WORK PENDING SO PLEASE DON'T REMOVE IT
}
//FOR ROW SET FORMAT END
}
}
}
public function save_info(){
$control_whr_cond = "";
$control_join_cond = "";
if($this->control_name === "custom_employees"){
$this->control_name = 'employees';
$control_name = 'employees';
$control_whr_cond = ' and cw_form_setting.onboard_input = "1"';
$control_join_cond = ' inner join cw_form_setting on cw_form_setting.input_for = cw_form_view_setting.prime_form_view_id';
}
$pro_qry = [];
$pro_qry[] = array("return"=>"form_info","qry"=>'select * from cw_form_setting where prime_module_id = "'.$control_name.'" and field_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for)'.$control_whr_cond.' ORDER BY input_for,field_sort asc');
//Get Company Info
$pro_qry[] = array("return"=>"company_info","qry"=>'select * from cw_company_information where cw_company_information.trans_status = 1');
//Email Count info - check email count exist or not
$pro_qry[] = array("return"=>"email_count_info","qry"=>'select count(*) as email_count from cw_modules where cw_modules.module_id = "'.$control_name.'" and cw_modules.email_setting = 1 and cw_modules.trans_status = 1');
if(count($pro_qry ?? []) > 0){
$save_info_rslt = $this->run_multi_qry($pro_qry);
if(!$save_info_rslt->sts){
$this->module_sts = false;
}else{
$this->form_info = $save_info_rslt->rslt->form_info;
}
}
$this->company_info = $save_info_rslt->rslt->company_info;
$this->email_count = $save_info_rslt->rslt->email_count_info[0]->email_count;
}
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;
$prime_module_id = $setting->prime_module_id;
$input_view_type = (int)$setting->input_view_type;
$input_for = (int)$setting->input_for;
$field_type = (int)$setting->field_type;
$label_id = $setting->label_name;
$label_name = ucwords($setting->view_name);
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$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;
$file_type = (int)$setting->file_type;
$mandatory_field = (int)$setting->mandatory_field;
$unique_field = (int)$setting->unique_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;
$pick_list_import = (int)$setting->pick_list_import;
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);
}
$where_condition = str_replace(',','","',$where_condition);
};
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,CONCAT_WS(\" ~ \", $pick_display) as $pick_list_val_2";
$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);
}
}
}
}
}
//FOR DEPENDENT AUTOCOMPLETE START (ONLY USING FOR FORM EDIT OPTION)
public function get_depen_auto_list_qry($depen_auto_pick_list,$prime_id){
$pro_qry = [];
foreach($depen_auto_pick_list as $setting){
$prime_form_id = (int)$setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_view_type = (int)$setting->input_view_type;
$input_for = (int)$setting->input_for;
$field_type = (int)$setting->field_type;
$label_id = $setting->label_name;
$label_name = ucwords($setting->view_name);
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$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;
$file_type = (int)$setting->file_type;
$mandatory_field = (int)$setting->mandatory_field;
$unique_field = (int)$setting->unique_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;
$pick_list_import = (int)$setting->pick_list_import;
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);
}
$where_condition = str_replace(',','","',$where_condition);
};
if($field_isdefault === 1){ // UDY NEED CHECK
if($field_type === 9){
$pick_display = $pick_table.'.'.$pick_display;
$pick_display = str_replace(",",",$pick_table.",$pick_display);
$pick_display_val = "CONCAT(".str_replace(",",'," ~ ",',$pick_display).") as auto_list_pick";
$pick_qry = 'select '.$pick_table.'.'.$auto_prime_id.','.$pick_table.'.'.$auto_dispaly_value.','.$pick_display_val .' from '. $pick_table .' inner join '.$this->prime_table. ' on '.$this->prime_table.'.'.$label_id.' = '. $pick_table .'.'.$auto_prime_id.' where '.$pick_table.'.trans_status = 1 and '.$this->prime_table.'.trans_status = 1 and '.$this->prime_table.'.'.$this->prime_id.' = "'.$prime_id.'"';
$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 === 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->depen_auto_list[$label_id] = $final_pick;
}
}
}
}
}
//FOR DEPENDENT AUTOCOMPLETE END (ONLY USING FOR FORM EDIT OPTION)
//FOR VIEW FORM CONDITION LIST(BY BUILD A JS CODE)
public function form_condition_list($form_condition_rslt){
$load_script = "";
foreach($form_condition_rslt as $condition){
$prime_cond_id = $condition->prime_cond_id;
$cond_module_id = $condition->cond_module_id;
$condition_label_name = strtolower(str_replace(" ","_",$condition->condition_label_name));
$condition_type = (int)$condition->condition_type;
$condition_check_form = explode(",",$condition->condition_check_form ?? "");
$condition_bind_to = explode(",",$condition->condition_bind_to ?? "");
$on_bind_inputs = "";
$on_change_inputs = "";
$on_blur_inputs = "";
foreach($condition_check_form as $label_name){
$cond_from_qry = 'select field_type,label_name from cw_form_setting where prime_module_id = "'.$this->control_name.'" and label_name in ("'.$label_name.'") ORDER BY input_for,field_sort asc';
$cond_from_qry = rtrim($cond_from_qry," ");
$pro_qry[] = array("return"=>$label_name,"qry"=>$cond_from_qry,"pro_info"=>array('label_name' => $label_name,'prime_cond_id' => $prime_cond_id,'cond_module_id' => $cond_module_id,'condition_label_name' => $condition_label_name,'condition_type' => $condition_type,'condition_check_form' => $condition_check_form,'condition_bind_to' => $condition_bind_to,));
}
}
if(count($pro_qry ?? []) > 0){
$form_cond_info = $this->run_multi_qry($pro_qry);
if(!$form_cond_info->sts){
$this->module_sts = false;
}else{
$form_cond_rslt = [];
$form_cond_rslt = json_decode(json_encode($form_cond_info->rslt),true);
foreach($form_cond_rslt as $cond_key=>$cond_info){
$is_exist_pro_qry = array_search($cond_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"];
$field_type = (int)$cond_info[0]['field_type'];
$label_name = $pro_info['label_name'];
$prime_cond_id = $pro_info['prime_cond_id'];
$cond_module_id = $pro_info['cond_module_id'];
$condition_label_name = $pro_info['condition_label_name'];
$condition_type = (int)$pro_info['condition_type'];
$condition_check_form = $pro_info['condition_check_form'];
$condition_bind_to = $pro_info['condition_bind_to'];
/*============
NOTE: AUTO COMPLETE BOX & FILE UPLOAD BOX ARE NOT INCLUDED IN ON LOAD SCRIPT
============*/
if(($field_type === 1) || ($field_type === 2) || ($field_type === 3) || ($field_type === 11) || ($field_type === 12)){
$on_bind_inputs .= "$label_name,";
}else
if(($field_type === 5) || ($field_type === 6) || ($field_type === 7) || ($field_type === 9)){
$on_change_inputs .= "$label_name,";
}
if($condition_type === 2){
if(($field_type === 4) || ($field_type === 13)){
$on_blur_inputs .= "$label_name,";
}
}
}
$check_input = "";
if($on_bind_inputs){
$on_bind_input = rtrim($on_bind_inputs,',');
$on_bind_input = str_replace(",",",#",$on_bind_input);
$on_bind_input = "#".$on_bind_input;
$check_input .= "$on_bind_input";
}
if($on_change_inputs){
$on_change_input = rtrim($on_change_inputs,',');
$on_change_input = str_replace(",",",#",$on_change_input);
$on_change_input = "#".$on_change_input;
if($check_input){
$check_input .= ",$on_change_input";
}else{
$check_input .= "$on_change_input";
}
}
if($on_blur_inputs){
$on_blur_input = rtrim($on_blur_inputs,',');
$on_blur_input = str_replace(",",",#",$on_blur_input);
$on_blur_input = "#".$on_blur_input;
if($check_input){
$check_input .= ",$on_blur_input";
}else{
$check_input .= "$on_blur_input";
}
}
$send_for = implode(",",$condition_check_form ?? []);
$send_data = "prime_cond_id:$prime_cond_id,for_input:'$send_for',";
foreach($condition_check_form as $check_form){
$send_data .= "$check_form:$('#$check_form').val(),";
}
$send_data = "{".rtrim($send_data,',')."}";
//BIND VALUE FROM DATABASE TABLE
if($condition_type === 1){
if($on_bind_input){
$fill_input = "";
foreach($condition_bind_to as $bind_to){
$fill_val = "ui.item.".$bind_to;
$fill_input .= "$('#$bind_to').val($fill_val);\n";
}
$send_url = site_url("custom_approval/bind_autocomplete_suggest");
$load_script .= "$('$on_bind_input').autocomplete({
source: function(request, response) {
$.getJSON('$send_url',{term:request.term, prime_cond_id:$prime_cond_id },response);
},
minChars:2,
autoFocus: true,
delay:10,
appendTo: '.modal-content',
select: function(e, ui) {
$fill_input
return false;
}
});\n";
}
if($on_change_input){
$fill_input = "";
foreach($condition_bind_to as $bind_to){
$fill_val = "rslt[0].".$bind_to;
$fill_input .= "$('#$bind_to').val($fill_val);\n";
}
$send_url = site_url("custom_approval/bind_change_suggest");
$load_script .= "$('$on_change_input').change(function(){
var isValid = true;
$('$on_change_input').each(function() {
if ($(this).val() === '') {
isValid = false;
$(this).addClass('error');
}else{
$(this).removeClass('error');
}
});
if(isValid){
$.ajax({
type: 'POST',
url: '$send_url',
data:$send_data,
success: function(data) {
var rslt = JSON.parse(data);
$fill_input
}
});
}
});\n";
}
}else //FOR WRITE CONDITIONS TYPE
if($condition_type === 2){
$fill_input = "";
foreach($condition_bind_to as $bind_to){
$fill_val = "rslt.".$bind_to;
$fill_input .= "$('#$bind_to').val($fill_val);\n $('#$bind_to').trigger('change');";
}
$send_url = site_url("custom_approval/calculation_suggest");
$encKey = $this->generateKey();
//CODE FOR JS FUNCTION ADD
$function_info = "function $condition_label_name(input_id){
var isValid = true;
$('#'+input_id).each(function() {
if ($(this).val() === '') {
isValid = false;
$(this).addClass('error');
}else{
$(this).removeClass('error');
}
});
if(isValid){
var encKey = '$encKey';
var encData = encrypt(encKey,$send_data);
$.ajax({
type: 'POST',
url: '$send_url',
data:encData,
success: function(data) {
var rslt = JSON.parse(data);
$fill_input
}
});
}
}";
if($on_bind_input){
$load_script .= "$('$on_bind_input').bind('keyup blur change', function(e) {
var input_id = $(this).attr('id');
$condition_label_name(input_id);
});\n";
}
if($on_change_input){
$load_script .= "$('$on_change_input').change(function(){
var input_id = $(this).attr('id');
$condition_label_name(input_id);
});\n";
}
if($on_blur_input){
$load_script .= "$('$on_blur_input').blur(function(){
var input_id = $(this).attr('id');
$condition_label_name(input_id);
});\n";
}
$load_script .= "\n$function_info\n";
}
}
}
}
$load_script .= "\nlet status = document.getElementsByClassName('modal-title')[0];
window.addEventListener('load', function(e){
if (navigator.onLine) {
//status.innerHTML = 'User is online';
status.classList.add('success');
status.classList.add('success');
} else {
status.innerHTML = 'Please Check Your Internet Connection...';
status.classList.remove('success');
status.classList.add('internet_modal_error');
}
}, false);
window.addEventListener('online', function(e){
// status.innerHTML = 'User is back online';
status.classList.remove('internet_modal_error');
status.classList.add('success');
location.reload();
}, false);
window.addEventListener('offline', function(e){
status.innerHTML = 'Please Check Your Internet Connection...';
status.classList.remove('success');
status.classList.add('internet_modal_error');
}, false);\n
$('.select2').on('select2:select', function(e){
$(this).focus();
e.preventDefault();
});\n";
if($load_script){
$this->form_condition_list[] = $load_script;
}
}
public function get_row_set_list($row_view_list,$row_form_list,$prime_id){
foreach($row_view_list as $view){
$prime_form_view_id = $view->prime_form_view_id;
$prime_view_module_id = $view->prime_view_module_id;
$form_view_label_name = $view->form_view_label_name;
$div_id = $form_view_label_name."_div_".$prime_form_view_id;
$table_id = $form_view_label_name."_tbl_".$prime_form_view_id;
$table_name = $this->control_name."_".$form_view_label_name;
$row_prime_id = "prime_".$table_name."_id";
$table_name = $this->db->dbprefix($table_name);
$table_prime_id = "prime_".$this->control_name."_id";
$table_head = array();
$thead_line = "";
$select_query = "$table_name.$row_prime_id,$table_name.$table_prime_id,";
$input_form_list = $row_form_list[$prime_form_view_id];
foreach($input_form_list as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
$date_type = $form->date_type;
if((int)$field_type === 4){
if((int)$date_type === 1){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else{
$select_query .= "$table_name.$label_name , ";
}
}else
if((int)$field_type === 13){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y H:i:s") as '.$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_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else{
$select_query .= "$table_name.$label_name , ";
}
$table_head[] = $label_name;
$thead_line .= "<th>$view_name</th>";
}
$thead = "<tr>$thead_line<th>Option</th></tr>";
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
$order_qry = 'order by abs('.$table_name.'.'.$row_prime_id.') desc';
$final_qry = 'select '.$select_query.' from '.$table_name.' '.$pick_query.' where '.$table_name.'.'.$table_prime_id.' = "'.$prime_id.'" and '.$table_name.'.trans_status = "1" '.$order_qry;
$pro_qry[$prime_form_view_id] = array("return"=>$prime_form_view_id,"qry"=>$final_qry,"pro_info"=>array('table_head' => $table_head,'row_prime_id' => $row_prime_id,'form_view_label_name' => $form_view_label_name,'prime_form_view_id' => $prime_form_view_id,'table_id' => $table_id,'div_id' => $div_id,'thead' => $thead,));
}
if(count($pro_qry ?? []) > 0){
$row_set_info = $this->run_multi_qry($pro_qry);
if(!$row_set_info->sts){
$this->module_sts = false;
}else{
$row_input_rslt = [];
$row_input_rslt = json_decode(json_encode($row_set_info->rslt));
$tr_line = "";
$row_view_array = [];
foreach($row_input_rslt as $key => $data){
$td_line = "";
$pro_info = $pro_qry[$key]["pro_info"];
$table_head = $pro_info['table_head'];
$row_prime_id = $pro_info['row_prime_id'];
$form_view_label_name = $pro_info['form_view_label_name'];
$prime_form_view_id = $pro_info['prime_form_view_id'];
$table_id = $pro_info['table_id'];
$div_id = $pro_info['div_id'];
$thead = $pro_info['thead'];
if($prime_form_view_id){
if(count((array)$data ?? []) > 0){
foreach($table_head as $label){
$datas_exp = $data->$label;
$rowset_form_info = $this->get_rowset_form_info();
$field_type = $rowset_form_info[$label]['field_type'];
$value = $data->$label;
if((int)$field_type === 4 || (int)$field_type === 13){
if($value === "00-00-0000" || $value === 'NULL' || $value === "00-00-0000 00:00:00"){
$td_line .= "<td></td>";
}else{
$td_line .= "<td>$value</td>";
}
}else
if((int)$field_type === 8){//textbox only
$value = str_replace("~",'"',$value);
$value = str_replace("`","'",$value);
$value = str_replace("^","&",$value);
$td_line .= "<td>$value</td>";
}else
if((int)$field_type === 10){
if($datas_exp === "" || $datas_exp === NULL){
$td_line .= "<td>No Preview Available</td>";
}else{
$td_line .= "<td><a href='$datas_exp' format.pdf='' target='_blank' style='cursor: pointer;''><i class='fa fa-download' style='color:red;' aria-hidden='true'></i></a></td>";
}
}else{
$td_line .= "<td>$value</td>";
}
}
$row_id = $data->$row_prime_id;
$tab_name = $this->control_name."_".$form_view_label_name;
$illustration_btn = "";
if($form_view_label_name === "eligibility_information"){
$illustration_btn = "<a class='btn btn-edit btn-xs row_btn' onclick = add_illustration('$row_id','$tab_name','$prime_form_view_id');>Illustration</a>";
}
$edit_btn = "<a class='btn btn-edit btn-xs row_btn' onclick = row_set_edit('$row_id','$tab_name','$prime_form_view_id','$prime_id');>Edit</a>";
$remove_btn = "<a class='btn btn-danger btn-xs row_btn' onclick = row_set_remove('$row_id','$tab_name','$prime_form_view_id','$prime_id');>Delete</a>";
$tr_line = "<tr>$td_line<td>$illustration_btn $edit_btn $remove_btn</td></tr>";
}
$row_set_view = "<table id='$table_id' class='table table-bordered' style='background-color: #FFFFFF; box-shadow: none;'>
<thead>$thead</thead>
<tbody>$tr_line</tbody>
</table>";
$row_view_array[$prime_form_view_id] = array('div_id' => $div_id, 'table_id' => $table_id,'row_set_view'=>$row_set_view);
}
}
return $row_view_array;
}
}
}
/******************** UDY END *************************/
//PROVIDE BASE DATA FOR MODULE
public function collect_base_info(){
$this->control_name = 'employees';
$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_emp_code = $this->session->userdata('logged_emp_code');
$this->logged_zone = $this->session->userdata('logged_zone');
$this->logged_site_location = $this->session->userdata('logged_site_location');
$this->logged_site_name = $this->session->userdata('logged_site_name');
$this->logged_cluster_name = $this->session->userdata('logged_cluster_name');
$this->prime_id = "prime_custom_".$this->control_name."_id";
$this->prime_table = "cw_custom_".$this->control_name;
$this->base_query = "select @SELECT@ from $this->prime_table";
$this->select_query = "$this->prime_table.$this->prime_id,";
$this->get_quick_link();
$this->get_table_head();
$this->get_view_info();
$this->get_form_info();
$this->get_rowset_form_info();
$this->get_role_condition();
$this->get_query_and_drop();
$this->get_condition();
$this->get_freeze_count();
$this->get_email_setting();
$this->company_info();
$this->role_based_condition();
$this->coloring_label();
}
/* ==============================================================*/
/* =================== BASE FUNCTIONS - START ===================*/
/* ==============================================================*/
// PROVIDE QUICK LINK LIST VIEW
public function get_quick_link(){
$link_query = 'select quicklink from cw_modules where module_id = "'.$this->control_name.'"';
$link_info = $this->db->query("CALL sp_a_run ('SELECT','$link_query')");
$link_result = $link_info->result();
$link_info->next_result();
$this->quick_link = $link_result[0];
}
// PROVIDE TABLE VIEW
public function get_table_head(){
$table_query = 'select label_name,view_name,field_type,date_type from cw_form_setting where prime_module_id = "'.$this->control_name.'" and input_view_type IN (1,2) and table_show = "1" and trans_status =1 ORDER BY table_sort asc';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$result = $table_info->result();
$table_info->next_result();
$this->table_head = $result;
$select_key = array_column($result ?? [], "label_name");
$this->select_query .= implode(",",$select_key ?? []);
}
// PROVIDE MODLE VIEWS
public function get_view_info(){
$view_query = 'select * from cw_form_view_setting where prime_view_module_id = "'.$this->control_name.'" and form_view_show = "1" and trans_status =1 and FIND_IN_SET("'.$this->logged_user_role.'",view_user_right) ORDER BY form_view_sort asc';
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_query')");
$view_result = $view_data->result();
$view_data->next_result();
$this->view_info = $view_result;
}
// PROVIDE TABLE VIEW
public function get_freeze_count(){
$freeze_table_query = 'select left_freeze,right_freeze from cw_modules where module_id = "'.$this->control_name.'" and trans_status = "1"';
$freeze_table_info = $this->db->query("CALL sp_a_run ('SELECT','$freeze_table_query')");
$freeze_result = $freeze_table_info->result();
$freeze_table_info->next_result();
return $freeze_result;
}
// PROVIDE MODLE FORM INPUT VIEWS
public function get_form_info(){
$from_query = 'select * from cw_form_setting where prime_module_id = "'.$this->control_name.'" and field_show = "1" and trans_status =1 and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) ORDER BY input_for,field_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result();
$form_data->next_result();
$this->form_info = $form_result;
}
public function get_rowset_form_info(){
if($this->control_name === "custom_employees"){
$this->control_name = 'employees';
}
$table_query = 'select label_name,view_name,field_type from cw_form_setting where prime_module_id = "'.$this->control_name.'" and input_view_type IN (3) and table_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) ORDER BY table_sort asc';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$result = $table_info->result_array();
$table_info->next_result();
$result = array_map(function($v){
return [$v['label_name'] => $v];
}, $result);
$result = call_user_func_array('array_merge', $result);
$this->rowset_form_info = $result;
return $result;
}
// PROVIDE MODLE TABLE ROLE BASED SEARCH
public function get_role_condition(){
$table_search_query = 'select where_condition from cw_form_table_search where query_module_id = "custom_approval" and query_for = "'.$this->logged_user_role.'" 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);
$session_query = 'select session_value from cw_session_value where session_for = 1 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"){
$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);
if($this->logged_user_role == 4){
$this->role_condition = $where_condition;
}else{
$this->role_condition = $where_condition;
}
//echo "BSK :: $this->role_condition"; die;
}
}
// PROVIDE ROLE BASED PICK LIST /*UDY-13-02-2020*/
public function get_role_based_picklist($query_list_id,$module_id){
$pick_query = 'select pick_where_condition from cw_pick_base_search where pick_module_id = "'.$module_id.'" and query_list_id = "'.$query_list_id.'" and pick_query_for = "'.$this->logged_user_role.'" and trans_status = "1"';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$where_condition = "";
if($pick_result){
$where_condition = str_replace('^','"',$pick_result[0]->pick_where_condition);
$where_condition = explode(',', $where_condition ?? "");
$where_condition = implode('","', $where_condition ?? []);
$session_query = 'select session_value from cw_session_value where session_for = 1 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"){
$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);
return $where_condition;
}
//PROVIDE SEARCH AND VIEW SELECT QUERY, DROPDOWN VALUES AND SEARCH FLITERS
public function get_query_and_drop(){
$this->view_select = "$this->prime_table.$this->prime_id,";
foreach($this->form_info as $setting){
$prime_form_id = (int)$setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_view_type = (int)$setting->input_view_type;
$input_for = (int)$setting->input_for;
$field_type = (int)$setting->field_type;
$label_id = $setting->label_name;
$label_name = ucwords($setting->view_name);
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$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;
$file_type = (int)$setting->file_type;
$mandatory_field = (int)$setting->mandatory_field;
$unique_field = (int)$setting->unique_field;
$search_show = (int)$setting->search_show;
$default_value = (int)$setting->default_value;
$pick_display = $setting->pick_display_value;
$pick_list_import = (int)$setting->pick_list_import;
if($default_value === 0){
$default_value = "";
}
$pick_drop = array();
$pick_master = array();
$pick_key = array();
$pick_val = array();
$final_pick = array();
if($field_isdefault === 1){
if(($field_type === 5) || ($field_type === 7)){
$where_condition = $this->get_role_based_picklist($prime_form_id,$prime_module_id);
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,CONCAT_WS(\" - \", $pick_display) as $pick_list_val_2";
}
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_list 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();
if($pick_result){
$pick_key = array_column($pick_result ?? [], $pick_list_val_1);
$pick_val = array_column($pick_result ?? [], $pick_list_val_2);
$final_pick = array_combine( $pick_key ?? [], $pick_val ?? []);
}
$final_pick = str_replace("~",'"',$final_pick);
$final_pick = str_replace("`","'",$final_pick);
$final_pick = str_replace("^","&",$final_pick);
$final_pick = array("" => "---- $label_name ----") + $final_pick;
$this->master_pick[$label_id] = $final_pick;
//array_unshift($final_pick,"---- $label_name ----");
$this->all_pick[$prime_form_id] = $final_pick;
//IMPORT FUNCTION FOR MONTHLY INPUT FMS MODULE TO EXPORT A PICKLIST DATA(FOR USER IMPORT THAT EXPORTED DATA IN TIME SHEET MODULE)
$imp_pick_list = "";
if($pick_list_import === 1){
$imp_pick_list = "$imp_pick_list_val_1,CONCAT_WS(\" - \", $pick_display) as $imp_pick_list_val_2";
}else
if($pick_list_import === 2){
$imp_pick_list = "$imp_pick_list_val_2 as $imp_pick_list_val_1,CONCAT_WS(\" - \", $pick_display) as $imp_pick_list_val_2";
}
if($imp_pick_list){
$pick_imp_qry = "select $imp_pick_list from $pick_table where trans_status = 1";
$pick_imp_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_imp_qry')");
$pick_imp_rslt = $pick_imp_data->result();
$pick_imp_data->next_result();
// if($label_name === "role"){
// $label_name = "category";
// }
$pick_imp_key = array_column($pick_imp_rslt ?? [], $imp_pick_list_val_2);
$pick_imp_val = array_column($pick_imp_rslt ?? [], $imp_pick_list_val_1);
// if($pick_list_import === 1){
// $pick_imp_key = array_column($pick_result, $pick_list_val_2);
// $pick_imp_val = array_column($pick_result, $pick_list_val_1);
// }
$final_imp_pick = array_combine( $pick_imp_key ?? [], $pick_imp_val ?? []);
$this->export_pick_arr[$label_id] = $final_imp_pick;
}
//THIS IS GET A EMPLOYEE ALL PICKLISTS
$get_components = 'select prime_form_id,prime_module_id,label_name,view_name from `cw_form_setting` where prime_module_id = "employees" and field_type = 5 ORDER BY label_name';
$get_components_info = $this->db->query("CALL sp_a_run ('SELECT','$get_components')");
$get_components_result = $get_components_info->result();
$get_components_info->next_result();
$components[""] = "---- Select Component ----";
foreach($get_components_result as $component){
$prime_form_id = $component->prime_form_id;
$components_name = strtoupper($component->view_name);
$this->components[$this->xss_clean($prime_form_id)] = $this->xss_clean($components_name);
// $this->emp_label_arr[$this->xss_clean($prime_form_id)] = $this->xss_clean($component->label_name);
}
}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 $where_condition";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
if($pick_result){
$pick_key = array_column($pick_result ?? [], $pick_list_val_1);
$pick_val = array_column($pick_result ?? [], $pick_list_val_2);
$final_pick = array_combine( $pick_key ?? [], $pick_val ?? []);
}
$final_pick = str_replace("~",'"',$final_pick);
$final_pick = str_replace("`","'",$final_pick);
$final_pick = str_replace("^","&",$final_pick);
$final_pick = array("" => "---- $label_name ----") + $final_pick;
$this->master_pick[$label_id] = $final_pick;
//array_unshift($final_pick,"---- $label_name ----");
$this->all_pick[$prime_form_id] = $final_pick;
}
}else
if($field_type === 9){
$pick_display_val = "CONCAT(".str_replace(",",'," - ",',$pick_display).") as auto_list_pick";
$pick_query = "select $auto_prime_id,$auto_dispaly_value,$pick_display_val 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();
if($pick_result){
$pick_key = array_column($pick_result ?? [], $auto_prime_id);
$pick_val = array_column($pick_result ?? [], 'auto_list_pick');
$final_pick = array_combine( $pick_key ?? [], $pick_val ?? []);
}
$final_pick = str_replace("~",'"',$final_pick);
$final_pick = str_replace("`","'",$final_pick);
$final_pick = str_replace("^","&",$final_pick);
$this->master_pick[$label_id] = $final_pick;
// $final_pick = array("" => "---- $label_name ----") + $final_pick;
$this->all_pick[$prime_form_id] = $final_pick;
$pick_imp_key = array_column($pick_result ?? [], 'auto_list_pick');
$pick_imp_val = array_column($pick_result ?? [], $auto_dispaly_value);
$final_imp_pick = array_combine( $pick_imp_key ?? [], $pick_imp_val ?? []);
$this->export_pick_arr[$label_id] = $final_imp_pick;
}
if(($input_view_type === 1) || ($input_view_type === 2)){
$this->view_select .= "$this->prime_table.$label_id,";
if($search_show === 1){
$this->fliter_list[] = array('label_id'=> $label_id, 'label_name'=> $label_name, 'field_isdefault'=> $field_isdefault, 'array_list'=> $final_pick, 'field_type'=> $field_type);
}
}
}
}
$this->view_select = rtrim($this->view_select,',');
// ONLY FOR EMPLOYEE
if($this->control_name === "employees"){
$this->view_select = $this->view_select .',user_name,password';
}
}
// PROVIDE MODLE ONLOAD CONDITION & FORMULA
public function get_condition(){
$condition_query = 'select * from cw_form_condition_formula where cond_module_id = "'.$this->control_name.'" and trans_status = "1" and FIND_IN_SET("'.$this->logged_role.'",condition_for)';
$condition_data = $this->db->query("CALL sp_a_run ('SELECT','$condition_query')");
$condition_result = $condition_data->result();
$condition_data->next_result();
$load_script = "";
foreach($condition_result as $condition){
$prime_cond_id = $condition->prime_cond_id;
$cond_module_id = $condition->cond_module_id;
$condition_label_name = strtolower(str_replace(" ","_",$condition->condition_label_name));
$condition_type = (int)$condition->condition_type;
$condition_check_form = explode(",",$condition->condition_check_form ?? "");
$condition_bind_to = explode(",",$condition->condition_bind_to ?? "");
$on_bind_input = "";
$on_change_input = "";
$on_blur_input = "";
foreach($condition_check_form as $label_name){
$cond_from_query = 'select field_type,label_name from cw_form_setting where prime_module_id = "'.$this->control_name.'" and label_name in ("'.$label_name.'") ORDER BY input_for,field_sort asc';
$cond_form_data = $this->db->query("CALL sp_a_run ('SELECT','$cond_from_query')");
$cond_form_result = $cond_form_data->result();
$cond_form_data->next_result();
$field_type = (int)$cond_form_result[0]->field_type;
/*============
NOTE: AUTO COMPLETE BOX & FILE UPLOAD BOX ARE NOT INCLUDED IN ON LOAD SCRIPT
============*/
if(($field_type === 1) || ($field_type === 2) || ($field_type === 3) || ($field_type === 11) || ($field_type === 12)){
$on_bind_input .= "$label_name,";
}else
if(($field_type === 5) || ($field_type === 6) || ($field_type === 7)){
$on_change_input .= "$label_name,";
}
if($condition_type === 2){
if(($field_type === 4) || ($field_type === 13)){
$on_blur_input .= "$label_name,";
}
}
}
$check_input = "";
if($on_bind_input){
$on_bind_input = rtrim($on_bind_input,',');
$on_bind_input = str_replace(",",",#",$on_bind_input);
$on_bind_input = "#".$on_bind_input;
$check_input .= "$on_bind_input";
}
if($on_change_input){
$on_change_input = rtrim($on_change_input,',');
$on_change_input = str_replace(",",",#",$on_change_input);
$on_change_input = "#".$on_change_input;
if($check_input){
$check_input .= ",$on_change_input";
}else{
$check_input .= "$on_change_input";
}
}
if($on_blur_input){
$on_blur_input = rtrim($on_blur_input,',');
$on_blur_input = str_replace(",",",#",$on_blur_input);
$on_blur_input = "#".$on_blur_input;
if($check_input){
$check_input .= ",$on_blur_input";
}else{
$check_input .= "$on_blur_input";
}
}
$send_for = implode(",",$condition_check_form ?? []);
$send_data = "prime_cond_id:$prime_cond_id,for_input:'$send_for',";
foreach($condition_check_form as $check_form){
$send_data .= "$check_form:$('#$check_form').val(),";
}
$send_data = "{".rtrim($send_data,',')."}";
//BIND VALUE FROM DATABASE TABLE
if($condition_type === 1){
if($on_bind_input){
$fill_input = "";
foreach($condition_bind_to as $bind_to){
$fill_val = "ui.item.".$bind_to;
$fill_input .= "$('#$bind_to').val($fill_val);\n";
}
$send_url = site_url("custom_approval/bind_autocomplete_suggest");
$load_script .= "$('$on_bind_input').autocomplete({
source: function(request, response) {
$.getJSON('$send_url',{term:request.term, prime_cond_id:$prime_cond_id },response);
},
minChars:2,
autoFocus: true,
delay:10,
appendTo: '.modal-content',
select: function(e, ui) {
$fill_input
return false;
}
});\n";
}
if($on_change_input){
$fill_input = "";
foreach($condition_bind_to as $bind_to){
$fill_val = "rslt[0].".$bind_to;
$fill_input .= "$('#$bind_to').val($fill_val);\n";
}
$send_url = site_url("custom_approval/bind_change_suggest");
$load_script .= "$('$on_change_input').change(function(){
var isValid = true;
$('$on_change_input').each(function() {
if ($(this).val() === '') {
isValid = false;
$(this).addClass('error');
}else{
$(this).removeClass('error');
}
});
if(isValid){
$.ajax({
type: 'POST',
url: '$send_url',
data:$send_data,
success: function(data) {
var rslt = JSON.parse(data);
$fill_input
}
});
}
});\n";
}
}else
if($condition_type === 2){
$fill_input = "";
foreach($condition_bind_to as $bind_to){
$fill_val = "rslt.".$bind_to;
$fill_input .= "$('#$bind_to').val($fill_val);\n $('#$bind_to').trigger('change');";
}
$encKey = $this->generateKey();
$send_url = site_url("custom_approval/calculation_suggest");
$function_info = "function $condition_label_name(){
var isValid = true;
$('$check_input').each(function() {
if ($(this).val() === '') {
isValid = false;
$(this).addClass('error');
}else{
$(this).removeClass('error');
}
});
if(isValid){
var encKey = '$encKey';
var encData = encrypt(encKey,$send_data);
$.ajax({
type: 'POST',
url: '$send_url',
data:encData,
success: function(data) {
var rslt = JSON.parse(data);
$fill_input
}
});
}
}";
if($on_bind_input){
$load_script .= "$('$on_bind_input').bind('keyup blur change', function(e) {
$condition_label_name();
});\n";
}
if($on_change_input){
$load_script .= "$('$on_change_input').change(function(){
$condition_label_name();
});\n";
}
if($on_blur_input){
$load_script .= "$('$on_blur_input').blur(function(){
$condition_label_name();
});\n";
}
$load_script .= "\n$function_info\n";
}
}
if($load_script){
$this->condition_list[] = $load_script;
}
}
/* ==============================================================*/
/* =================== BASE FUNCTIONS - END ===================*/
/* ==============================================================*/
/* ==============================================================*/
/* ================= CONDITION OPERATION - START ================*/
/* ==============================================================*/
//PROVIDE ALL SINGLE BOX AUTOCOMPLETE DROP DOWN
public function suggest(){
$search_term = $this->input->post_get('term');
$prime_form_id = $this->input->get('prime_form_id');
$depen_pick_list = $this->input->get('depen_pick_list');
$depend_hidden_id = $this->input->get('depend_hidden_id');
$label_id_val = $this->input->get('label_id');
$form_query = 'select * from cw_form_setting where prime_form_id in ("'.$prime_form_id.'","'.$depend_hidden_id.'") and trans_status = 1';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_query')");
$form_result = $form_data->result_array();
$form_data->next_result();
$form_result = array_reduce($form_result, function($result, $arr){
$result[$arr['prime_form_id']] = $arr;
return $result;
}, array());
if($form_result[$prime_form_id]){
$pick_table = $form_result[$prime_form_id]['pick_table'];
$pick_list = $form_result[$prime_form_id]['pick_list'];
$auto_prime_id = $form_result[$prime_form_id]['auto_prime_id'];
$auto_dispaly_value = $form_result[$prime_form_id]['auto_dispaly_value'];
$pick_display_value = $form_result[$prime_form_id]['pick_display_value'];
$depand_join = "";
$depand_where = "";
if((int)$depen_pick_list === 1){
$pick_table_sec = $form_result[$depend_hidden_id]['pick_table'];
$depand_tbl1 = $form_result[$prime_form_id]['table_column1'];
$depand_tbl2 = $form_result[$prime_form_id]['table_column2'];
// $depand_display = $form_result[$depend_hidden_id]['auto_dispaly_value'];
$field_type = $form_result[$depend_hidden_id]['field_type'];
$depen_pick_type = $form_result[$depend_hidden_id]['pick_list_type'];
$depen_pick_list = $form_result[$depend_hidden_id]['pick_list'];
if((int)$field_type === 5){
$pick_list_val = explode(",",$depen_pick_list ?? "");
if((int)$depen_pick_type === 1){
$depand_display = $pick_list_val[0];
}else
if((int)$depen_pick_type === 2){
$depand_display = $pick_table."_id";
}
}else
if((int)$field_type === 9){
$depand_display = $form_result[$depend_hidden_id]['auto_prime_id'];
}
// $depand_display = $form_result[$depend_hidden_id]['auto_prime_id'];
$pick_table = $this->input->get('pick_table');
$table_column2 = $this->input->get('table_column2');
$pick_tbl_val = $this->input->get('pick_tbl_val');
$depen_select = $this->input->get('depen_select');
$depand_join = " inner join $pick_table_sec on $pick_table_sec.$depand_tbl1 = $pick_table.$depand_tbl2";
$depand_where = " and $pick_table_sec.trans_status = 1 and $pick_table_sec.$depand_display = \"$depen_select\"";
}
$where_condition = $this->get_role_based_picklist($prime_form_id,$form_result[$prime_form_id]['prime_module_id']);
$pick_list = explode(",", $pick_list ?? "");
$pick_list = "$pick_table.".implode(",$pick_table.", $pick_list ?? []);
$pick_display_value = explode(",", $pick_display_value ?? "");
$pick_display_value = "$pick_table.".implode(",$pick_table.", $pick_display_value ?? []);
$auto_list = "CONCAT(".str_replace(",",'," - ",',$pick_list).") as auto_list";
$display_list = "CONCAT(".str_replace(",",'," - ",',$pick_display_value).") as display_list";
if($auto_prime_id){
$auto_prime_ids = "$pick_table.$auto_prime_id,";
}
if($auto_dispaly_value){
$auto_dispaly_values = "$pick_table.$auto_dispaly_value,";
}
$suggest_query = "select $auto_prime_ids $auto_dispaly_values $auto_list,$display_list from $pick_table $depand_join where $pick_table.trans_status = 1 $depand_where";
$col_list = explode(",",$pick_list ?? "");
$suggest_query .= " and ";
$where_query = "";
foreach($col_list as $col){
$search_term = str_replace('"',"~",$search_term);
$search_term = str_replace("'","`",$search_term);
$search_term = str_replace("&","^",$search_term);
$where_query .= $col.' like "%'.$search_term.'%" or ';
}
$where_query = rtrim($where_query," or ");
$suggest_query .= '('.$where_query.')';
$suggest_query .= $where_condition;
$suggest_query .= ' limit 0,50';
$suggest_data = $this->db->query("CALL sp_a_run ('SELECT','$suggest_query')");
$suggest_result = $suggest_data->result();
$suggest_data->next_result();
foreach($suggest_result as $result){
$suggest_prime_id = $result->$auto_prime_id;
$suggest_dispaly = $result->$auto_dispaly_value;
$suggest_list = $result->auto_list;
$suggest_list = str_replace("~",'"',$suggest_list);
$suggest_list = str_replace("`","'",$suggest_list);
$suggest_list = str_replace("^","&",$suggest_list);
$suggest_dispaly_list = $result->display_list;
$suggest_dispaly_list = str_replace("~",'"',$suggest_dispaly_list);
$suggest_dispaly_list = str_replace("`","'",$suggest_dispaly_list);
$suggest_dispaly_list = str_replace("^","&",$suggest_dispaly_list);
$suggestions[] = array('value' => $suggest_prime_id, 'label' => $suggest_list, 'display_name' => $suggest_dispaly_list);
}
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
//PROVIDE AUTOCOMPLETE DROP DOWN TO FILL MULTI INPUT BOX
public function bind_autocomplete_suggest(){
$search_term = $this->input->post_get('term');
$prime_cond_id = $this->input->post_get('prime_cond_id');
echo $this->get_bind_final_query("autocomplete",$search_term,$prime_cond_id);
}
//PROVIDE DATA WHILE ON CHANGE EVENT TO FILL MULTI INPUT BOX
public function bind_change_suggest(){
$for_input = $this->input->post_get('for_input');
$prime_cond_id = $this->input->post_get('prime_cond_id');
$search_term = $this->input->post_get($for_input);
echo $this->get_bind_final_query("change",$search_term,$prime_cond_id);
}
// UDY NEED TO REVIEW
//PROVIDE QUERY AND RESULT ARRAY TO BIND INPUT
public function get_bind_final_query($from,$search_term,$prime_cond_id){
/*============
NOTE: VAR FROM NOT USED MAY CAN USE IN FURTURE PROCESS
============*/
$cond_query = 'select * from cw_form_condition_formula where prime_cond_id = "'.$prime_cond_id.'" and trans_status = 1';
$cond_data = $this->db->query("CALL sp_a_run ('SELECT','$cond_query')");
$cond_result = $cond_data->result();
$cond_data->next_result();
$final_qry = "";
if($cond_result){
$cond_module_id = $cond_result[0]->cond_module_id;
$condition_check_form = $cond_result[0]->condition_check_form;
$condition_bind_to = $cond_result[0]->condition_bind_to;
$condition_table = $cond_result[0]->condition_table;
$table_query = 'select * from cw_form_table_cond_for where table_cond_id = "'.$prime_cond_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();
$line_table_query = "";
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;
$module_name = str_replace("cw_","",$line_prime_table);
$prime_id = "prime_".$module_name."_id";
$join_module_name = str_replace("cw_","",$line_join_table);
$join_prime_id = "prime_".$join_module_name."_id";
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_table_query){
$module_name = str_replace("cw_","",$condition_table);
$prime_id = "prime_".$module_name."_id";
$line_table_query = " $condition_table ";
}
$for_input_query = 'select * from cw_form_for_input where input_for_cond_id = "'.$prime_cond_id.'" and trans_status = 1';
$for_input_data = $this->db->query("CALL sp_a_run ('SELECT','$for_input_query')");
$for_input_result = $for_input_data->result();
$for_input_data->next_result();
$for_line_input_query = "";
foreach($for_input_result as $for_input){
$line_input_for = $for_input->line_input_for;
$line_input_for_table = $for_input->line_input_for_table;
$line_input_for_col = $for_input->line_input_for_col;
$for_line_input_query .= $line_input_for_col.' like "'.$search_term.'%" or ';
}
if($for_line_input_query){
$for_line_input_query = " where ". rtrim($for_line_input_query," or ");
}
$input_query = 'select * from cw_form_bind_input where input_cond_id = "'.$prime_cond_id.'" and trans_status = 1';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$line_input_query = "";
foreach($input_result as $input){
$line_input_bind_to = $input->line_input_bind_to;
$line_input_bind_table = $input->line_input_bind_table;
$line_input_bind_col = $input->line_input_bind_col;
$select_query = 'select field_type from cw_form_setting where prime_module_id = "'.$cond_module_id.'" and label_name = "'.$line_input_bind_to.'"';
$select_data = $this->db->query("CALL sp_a_run ('SELECT','$select_query')");
$select_result = $select_data->result();
$select_data->next_result();
$field_type = (int)$select_result[0]->field_type;
if(($field_type === 4) || ($field_type === 13)){
$line_input_query .= 'DATE_FORMAT('.$line_input_bind_col.',"%d-%m-%Y") as '.$line_input_bind_to.',';
}else{
$line_input_query .= "$line_input_bind_col as $line_input_bind_to,";
}
}
if($line_input_query){
$line_input_query = rtrim($line_input_query,',');
}else{
$line_input_query = " * ";
}
$final_qry = "select $line_input_query from $line_table_query $for_line_input_query";
}
if($final_qry){
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$line = array();
$lable = "";
foreach($input_result as $input){
$line_input_bind_to = $input->line_input_bind_to;
$rslt_val = $rslt->$line_input_bind_to;
$line[$line_input_bind_to] = $rslt_val;
if($rslt_val){
$lable .= "$rslt_val - ";
}
}
$lable = rtrim($lable," - ");
$line['value'] = '';
$line['label'] = $lable;
$suggestions[] = $line;
}
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
return json_encode($suggestions);
}
//PROVIDE DATA FOR ONCHANGE CALCUATION
public function calculation_suggest(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$for_input = $this->input->post_get('for_input');
$prime_cond_id = $this->input->post_get('prime_cond_id');
$cond_query = 'select * from cw_form_condition_formula where prime_cond_id = "'.$prime_cond_id.'" and trans_status = 1';
$cond_data = $this->db->query("CALL sp_a_run ('SELECT','$cond_query')");
$cond_result = $cond_data->result();
$cond_data->next_result();
$condition_check_form = explode(",",$cond_result[0]->condition_check_form ?? "");
$condition_bind_to = $cond_result[0]->condition_bind_to;
$condition_table = $cond_result[0]->condition_table;
$condition_type = $cond_result[0]->condition_type;
$is_drop_down = (int)$cond_result[0]->is_drop_down;
$cond_drop_down = $cond_result[0]->cond_drop_down;
$fianl_result_array = array();
if($is_drop_down === 1){
$search_term = $this->input->post_get($cond_drop_down);
$input_query = 'select * from cw_form_bind_input where input_cond_id = "'.$prime_cond_id.'" and line_input_bind_table = "'.$search_term.'"';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$line_input_bind_col = "";
foreach($input_result as $input){
$line_input_bind_to = $input->line_input_bind_to;
$line_input_bind_col = $input->line_input_bind_col;
$line_input_bind_col = str_replace("~","'",$line_input_bind_col);
$line_input_bind_col = str_replace("!",'"',$line_input_bind_col);
if($line_input_bind_col){
foreach($condition_check_form as $check_form){
if(strpos($line_input_bind_col,"@$check_form@") !== false) {
$value = $this->input->post_get($check_form);
if(strpos($check_form,"date") !== false) {
$value = new DateTime($value);
$value = $value->format("Y-m-d");
$value = "'$value'";
}
$line_input_bind_col = str_replace("@$check_form@",$value, $line_input_bind_col);
}
}
$dynamic_file_name= "calculation_file/".$line_input_bind_to."_".$this->logged_id.".php";
unlink("$dynamic_file_name");
$fname = $line_input_bind_to."(){";
$code = "<?php function $fname $line_input_bind_col }?>";
fopen("$dynamic_file_name", "w");
file_put_contents("$dynamic_file_name",$code);
chmod($dynamic_file_name, 0777);
require_once("$dynamic_file_name");
$fianl_result_array[$line_input_bind_to] = $line_input_bind_to();
unlink("$dynamic_file_name");
}
}
}else{
$input_query = 'select * from cw_form_bind_input where input_cond_id = "'.$prime_cond_id.'"';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$line_input_bind_col = "";
foreach($input_result as $input){
$line_input_bind_to = $input->line_input_bind_to;
$line_input_bind_col = $input->line_input_bind_col;
$line_input_bind_col = str_replace("~","'",$line_input_bind_col);
$line_input_bind_col = str_replace("!",'"',$line_input_bind_col);
if($line_input_bind_col){
foreach($condition_check_form as $check_form){
if(strpos($line_input_bind_col,"@$check_form@") !== false) {
$value = $this->input->post_get($check_form);
if(strpos($check_form,"date") !== false) {
$value = new DateTime($value);
$value = $value->format("Y-m-d");
$value = "'$value'";
}
$line_input_bind_col = str_replace("@$check_form@",$value, $line_input_bind_col);
}
}
$dynamic_file_name= "calculation_file/".$line_input_bind_to."_".$this->logged_id.".php";
unlink("$dynamic_file_name");
$fname = $line_input_bind_to."(){";
$code = "<?php function $fname $line_input_bind_col }?>";
fopen("$dynamic_file_name", "w");
file_put_contents("$dynamic_file_name",$code);
chmod($dynamic_file_name, 0777);
require_once("$dynamic_file_name");
$fianl_result_array[$line_input_bind_to] = $line_input_bind_to();
unlink("$dynamic_file_name");
}
}
}
echo json_encode($fianl_result_array);
}
// UDY NEED TO REVIEW
/* ==============================================================*/
/* ================= CONDITION OPERATION - END ==================*/
/* ==============================================================*/
/* ==============================================================*/
/* ================== ROWSET OPERATION - START ==================*/
/* ==============================================================*/
// ROWSET SAVE
public function rowset_save(){
// ENCRYPT DATAS WILL DECRYPT FUNCTIONALITY ADDED BY _ARN
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$view_id = $this->input->post('view_id');
$module_id = $this->input->post('module_id');
$row_prime_id = (int)$this->input->post('row_prime_id');
$row_label_name = $this->input->post('row_label_name');
$prime_id = (int)$this->input->post('prime_id');
$table_name = "custom_".$module_id."_".$row_label_name;
$table_prime = "prime_".$table_name."_id";
$table_name = $this->db->dbprefix($table_name);
$prime_qry_key = "prime_custom_".$module_id."_id,";
$prime_qry_value = '"'.$prime_id.'",';
$prime_upd_query = "";
$form_qry = 'select * from cw_form_setting where prime_module_id = "'.$module_id.'" and input_for = "'.$view_id.'" and field_show = "1" and trans_status = 1';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
foreach($form_result as $setting){
$field_type = $setting->field_type;
$input_view_type = (int)$setting->input_view_type;
$label_id = strtolower(str_replace(" ","_",$setting->label_name));
$field_isdefault = $setting->field_isdefault;
if((int)$field_type === 7){
$multi_name = $label_id."[]";
$value = implode(",",$this->input->post($multi_name) ?? []);
}else{
$value = $this->input->post($label_id);
}
if((int)$field_type === 4){
if((int)$date_type === 1){
$value = date('Y-m-d',strtotime($value));
}else{
$value = $value;
}
}else
if((int)$field_type === 13){
$value = date('Y-m-d H:i:s',strtotime($value));
}else
if((int)$field_type === 8){//textbox only
$value = str_replace('"',"~",$value);
$value = str_replace("'","`",$value);
}else
if((int)$field_type === 10){//File Upload
$value = $value;
$remove_upload_fname = "old_".$label_id;
$remove_upload_file = $this->input->post($remove_upload_fname);
if($value !== $remove_upload_file){
unlink($remove_upload_file);
}
}
$prime_qry_key .= $label_id.",";
$prime_qry_value .= '"'.$value.'",';
$prime_upd_query .= $label_id.' = "'.$value.'",';
$exist_qry .= $label_id.' = "'.$value.'" and ';
}
$created_on = date("Y-m-d h:i:s");
$exist_count = 0;
if((int)$row_prime_id === 0){
// INSERT QUERY TRANS STATUS COLUMN ADDED BY -AR
$prime_qry_key .= "trans_created_by,trans_created_date,trans_status";
$prime_qry_value .= '"'.$this->logged_id.'","'.$created_on.'","1"';
$prime_insert_query = "insert into $table_name ($prime_qry_key) values ($prime_qry_value)";
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$row_set_data = $this->get_row_set_data($view_id,$prime_id);
echo json_encode(array('success' => TRUE, 'message' => "Successfully added", 'insert_id' => $insert_id, 'row_set_data' => $row_set_data));
}else{
$prime_upd_query .= 'trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$prime_update_query = "UPDATE $table_name SET ". $prime_upd_query .' WHERE '. $table_prime .' = "'. $row_prime_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')");
$row_set_data = $this->get_row_set_data($view_id,$prime_id);
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated",'insert_id' => $row_prime_id,'row_set_data' => $row_set_data));
}
}
// UPLOAD FILES FUNCTION ADDED BY _ARN
public function upload_files(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$send_for = $this->input->post('send_for'); //import or upload
$send_from = $this->input->post('send_from'); //module name
$file_name = $this->input->post('fileName');
$file_data = base64_decode($this->input->post('file_data'));
$finfo = new finfo(FILEINFO_MIME_TYPE);
$file_type = $finfo->buffer($file_data); // Get the MIME type from the binary data
$module_id = '';
if($send_from == 'custom_approval'){
$module_id ='employees';
}
// Create directory if not exists
$upload_dir = "upload_files/$send_from";
if(($send_for !== "") && ($send_from !== "")){
if(!file_exists($upload_dir)){
mkdir($upload_dir, 0755, true);
chmod($upload_dir, 0755);
}
// Ensure the directory is writable
if (!is_writable($upload_dir)) {
echo json_encode(array('success' => false, 'msg' => "Error: Upload directory is not writable."));
exit(0);
}
$label_id = $this->input->post('label_id');
$file_size = $this->input->post('size');
//MIME Types
$mime_types = [ 'jpg' => 'image/jpeg', 'jpeg' => 'image/jpeg', 'png' => 'image/png', 'gif' => 'image/gif', 'html' => 'text/html', 'pdf' => 'application/pdf', 'doc' => 'application/msword', 'docx' => 'application/vnd.openxmlformats-officedocument.wordprocessingml.document', 'xls' => 'application/vnd.ms-excel', 'xlsx' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'mp3' => 'audio/mpeg', 'mp4' => 'video/mp4', 'zip' => 'application/zip', 'txt' => 'text/plain','msg' => 'application/vnd.ms-outlook','eml' => 'message/rfc822'];
//Get info from Form Settings
$from_query = 'select upload_extension,upload_file_size from cw_form_setting where prime_module_id = "'.$module_id.'" and field_show = "1" and field_type = 10 and label_name = "'.$label_id.'" and trans_status = "1" and FIND_IN_SET("'.$this->logged_role.'",field_for) ORDER BY input_for,field_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result();
$form_data->next_result();
if(count($form_result ?? []) === 0){
echo json_encode(array('success' => false, 'msg' =>"Url Expired.. Please refresh the page and try again...."));
exit(0);
}
$upload_filesize = $form_result[0]->upload_file_size;
$upload_extension = $form_result[0]->upload_extension;
$allowed_ext = explode(",",$upload_extension);
// Find elements in $array1 that match the keys in $array2
$array_flip = array_flip($allowed_ext);
$allowed_mimes = array_intersect_key($mime_types,$array_flip);
if(!in_array($file_type, $allowed_mimes)){
echo json_encode(array('success' => false, 'msg' =>"Please upload valid Mime type file such as $upload_extension"));
exit(0);
}
$file_name = str_replace(" ","_", $file_name);
$file_size = $file_size/1000;
if((int)$upload_filesize === 0){
$upload_filesize = 500;
}
if((int)$file_size <= (int)$upload_filesize){
if($file_name){
$ext = pathinfo($file_name, PATHINFO_EXTENSION);
if(in_array($ext, $allowed_ext)){
$random_digit = rand(0000,99999999999);
$new_file_name = "upload_files/$send_from/".$random_digit."_".$file_name;
$path = $this->sanitize_input($new_file_name, 10);
file_put_contents($path, $file_data);
echo json_encode(array('success' => true, 'msg' =>"File moved to server","path"=>$path));
}else{
echo json_encode(array('success' => false, 'msg' =>"Please upload valid file such as $upload_extension"));
}
}else{
echo json_encode(array('success' => false, 'msg' =>"Please upload valid file"));
}
}else{
echo json_encode(array('success' => false, 'msg' =>"File Size Must be below ".$upload_filesize."kb"));
}
}else{
echo json_encode(array('success' => false, 'msg' =>"Please refresh page and retry"));
}
}
//ROW SET EDIT DATA
public function row_set_edit(){
// ENCRYPT DATAS WILL DECRYPT FUNCTIONALITY ADDED BY _ARN
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$control_name = 'employees';
$row_id = (int)$this->input->post('row_id');
$view_id = (int)$this->input->post('view_id');
$table_name = $this->input->post('table_name');
$table_prime_id = "prime_".$table_name."_id";
$table_name = $this->db->dbprefix($table_name);
$final_qry = "select * from $table_name " .' where '.$table_prime_id.' = "'.$row_id.'" and trans_status = "1"';
$row_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$row_result = $row_data->result();
$row_data->next_result();
$form_qry = 'select * from cw_form_setting where prime_module_id = "'.$control_name.'" and input_for = "'.$view_id.'" and input_view_type = "3" and trans_status = "1"';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
$rslt_info = array();
$rslt_info[$table_prime_id] = array('input_value'=>$row_result[0]->$table_prime_id,'field_type'=>1); ;
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$label_name = $form->label_name;
$field_type = $form->field_type;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
$mandatory_field = (int)$form->mandatory_field;
$input_value = $row_result[0]->$label_name;
if((int)$field_type === 4){
if((int)$date_type === 1){
if($input_value === '0000-00-00' || $input_value ==='01-01-1970' || $input_value === 'NULL'){
$input_value = "";
}else{
$input_value = date('d-m-Y',strtotime($input_value));
}
}else{
$input_value = $input_value;
}
if($input_value === "01-01-1970" || $input_value === 'NULL' || $input_value === "0000-00-00"){
$input_value = "";
}
$rslt_info[$label_name] = array('input_value'=>$input_value,'field_type'=>$field_type,'mandatory_field'=>$mandatory_field);
}else
if((int)$field_type === 13){
$input_date = date('d-m-Y',strtotime($input_value));
if($input_date === "01-01-1970" || $input_date === 'NULL' || $input_date === '' || $input_date === "00-00-0000" || $input_date === '30-11--0001'){
$input_value = "";
}else{
$input_value = $input_value;
}
$rslt_info[$label_name] = array('input_value'=>$input_value,'field_type'=>$field_type,'mandatory_field'=>$mandatory_field);
}else
if((int)$field_type === 8){
$input_value = str_replace("~",'"',$input_value);
$input_value = str_replace("`","'",$input_value);
$input_value = str_replace("^","&",$input_value);
$rslt_info[$label_name] = array('input_value'=>$input_value,'field_type'=>$field_type,'mandatory_field'=>$mandatory_field);
}else
if((int)$field_type === 9){
$rslt_info[$label_name] = array('input_value'=>$input_value,'field_type'=>$field_type,'mandatory_field'=>$mandatory_field);
$pick_query = 'select '.$auto_dispaly_value.' from '.$pick_table.' where '.$auto_prime_id.' = "'.$input_value.'" and trans_status = 1';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$input_value = $pick_result[0]->$auto_dispaly_value;
$label_name = $label_name."_hidden_".$prime_form_id;
$rslt_info[$label_name] = array('input_value'=>$input_value,'field_type'=>$field_type,'mandatory_field'=>$mandatory_field);
}else
if((int)$field_type === 10){
$old_label_name = "old_".$label_name;
$rslt_info[$label_name] = array('input_value'=>$input_value,'field_type'=>$field_type,'mandatory_field'=>$mandatory_field);
$rslt_info[$old_label_name] = array('input_value'=>$input_value,'field_type'=>$field_type,'mandatory_field'=>$mandatory_field);
}else{
$rslt_info[$label_name] = array('input_value'=>$input_value,'field_type'=>$field_type,'mandatory_field'=>$mandatory_field);
}
}
echo json_encode(array('success' => TRUE, 'row_result' => $rslt_info,'table_name'=> $table_name));
}
//ROW SET REMOVE DATA
public function row_set_remove(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$row_id = (int)$this->input->post('row_id');
$view_id = (int)$this->input->post('view_id');
$table_name = $this->input->post('table_name');
$prime_id = $this->input->post('prime_id');
$table_prime_id = "prime_".$table_name."_id";
$table_name = $this->db->dbprefix($table_name);
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$final_qry = 'UPDATE '.$table_name.' SET trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" , trans_status = 0 WHERE '.$table_prime_id.' = "'.$row_id.'"';
$this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$row_set_data = $this->get_row_set_data($view_id,$prime_id);
echo json_encode(array('success' => TRUE, 'msg' => "Remove Successfully",'row_set_data' => $row_set_data));
}
public function rowset_remove_file(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$table_name = $this->input->post('table_name_set');
$row_id = (int)$this->input->post('row_id');
$input_name = $this->input->post('input_name');
$view_id = (int)$this->input->post('view_id');
$primes_id = (int)$this->input->post('primes_id');
$remove_cw = str_replace("cw_","",$table_name);
$primary_id = "prime_".$remove_cw."_id";
if($table_name){
$created_on = date("Y-m-d h:i:s");
$set_query = $input_name .' = "" ,trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$update_query = 'UPDATE '.$table_name .' SET '. $set_query .' WHERE '.$primary_id.' = "'. $row_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
$row_set_edit = $this->get_row_set_data($view_id,$primes_id);
$div_id = $row_set_edit['div_id'];
$table_id = $row_set_edit['table_id'];
$row_set_view = $row_set_edit['row_set_view'];
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated",'div_id' => $div_id, 'table_id' => $table_id,'row_set_view'=>$row_set_view));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to process your request"));
}
}
//PROVIDE ROWSET DATA BY ID
public function get_row_set_data($view_id,$prime_id){
$control_name = 'employees';
$view_qry = 'select * from cw_form_view_setting where prime_form_view_id = "'.$view_id.'" and prime_view_module_id = "'.$control_name.'" and form_view_type = "3" and trans_status = 1';
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_qry')");
$view_result = $view_data->result();
$view_data->next_result();
$prime_form_view_id = $view_result[0]->prime_form_view_id;
$prime_view_module_id = $view_result[0]->prime_view_module_id;
$form_view_label_name = $view_result[0]->form_view_label_name;
$div_id = $form_view_label_name."_div_".$prime_form_view_id;
$table_id = $form_view_label_name."_tbl_".$prime_form_view_id;
$table_name = "custom_".$control_name."_".$form_view_label_name;
$row_prime_id = "prime_".$table_name."_id";
$table_name = $this->db->dbprefix($table_name);
$table_prime_id = "prime_custom_".$control_name."_id";
$form_qry = 'select prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "'.$control_name.'" and input_for = "'.$prime_form_view_id.'" and input_view_type = "3" and table_show = "1" and trans_status = "1" order by abs(field_sort)';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
$table_head = array();
$thead_line = "";
$select_query = "$table_name.$row_prime_id,$table_name.$table_prime_id,";
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
if((int)$field_type === 13){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$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_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else{
$select_query .= "$table_name.$label_name , ";
}
$table_head[] = $label_name;
$thead_line .= "<th>$view_name</th>";
}
$thead = "<tr>$thead_line<th>Option</th></tr>";
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
$final_qry = "select $select_query from $table_name $pick_query " .' where '.$table_name.'.'.$table_prime_id.' = "'.$prime_id.'" and '.$table_name.'.trans_status = "1" order by abs('.$table_name.'.'.$row_prime_id.') desc';
$row_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$row_result = $row_data->result();
$row_data->next_result();
$tr_line = "";
foreach($row_result as $data){
$td_line = "";
foreach($table_head as $label){
$datas_exp = $data->$label;
//$total_counts = count($datas_exp ?? []);
$value = $data->$label;
$rowset_form_info = $this->get_rowset_form_info();
$field_type = $rowset_form_info[$label]['field_type'];
if((int)$field_type === 10){
if($datas_exp === "" || $datas_exp === NULL){
$td_line .= "<td>No Preview Available</td>";
}else{
//PDF FILE NAME DISPLAY
$file_ext_type = preg_replace('/^.*\.([^.]+)$/D', '$1', $value);
$split_values = explode("/", $value);
$file_name = $split_values[2];
$file_name = preg_replace('/^\d+_/', '', $file_name);
$file_name = strtolower($file_name);
if($file_ext_type === 'zip' || $file_ext_type === 'rar' || $file_ext_type === 'eml' || $file_ext_type === 'msg'){
$td_line .= "<td><a href='$datas_exp' format.pdf = '' target='_blank' download = $file_name style='cursor: pointer;color:blue;'><i class='fa fa-download' style='margin-right:5px' aria-hidden='true'></i>$file_name</a></td>";
}else{
$td_line .= '<td><a onclick="view_upload_file(\''.$value.'\', \''.$file_ext_type.'\');" style="cursor: pointer; color: blue;margin-right:5px"><i class="fa fa-folder-open" style="margin-right:5px"aria-hidden="true"></i>'.$file_name.'</a></td>';
}
}
}else{
$td_line .= "<td>$value</td>";
}
}
$row_id = $data->$row_prime_id;
$tab_name = "custom_".$control_name."_".$form_view_label_name;
$illustration_btn = "";
if($form_view_label_name === "eligibility_information"){
$illustration_btn = "<a class='btn btn-edit btn-xs row_btn' onclick = add_illustration('$row_id','$tab_name','$prime_form_view_id');>Illustration</a>";
}
$edit_btn = "<a class='btn btn-edit btn-xs row_btn' onclick = row_set_edit('$row_id','$tab_name','$prime_form_view_id','$prime_id');>Edit</a>";
$remove_btn = "<a class='btn btn-danger btn-xs row_btn' onclick = row_set_remove('$row_id','$tab_name','$prime_form_view_id','$prime_id');>Delete</a>";
$tr_line .= "<tr>$td_line<td>$illustration_btn $edit_btn $remove_btn</td></tr>";
}
$row_set_view = "<table id='$table_id' class='table table-bordered' style='background-color: #FFFFFF; box-shadow: none;'>
<thead>$thead</thead>
<tbody>$tr_line</tbody>
</table>";
return array('div_id' => $div_id, 'table_id' => $table_id,'row_set_view'=>$row_set_view);
}
/* ==============================================================*/
/* =================== ROWSET OPERATION - END ===================*/
/*
==============================================================*/
//print data print
public function load_print_data($print_doc_id,$view_id){
$data['print_sts'] = false;
$design_qry = 'select print_design,print_type from cw_print_design inner join cw_print_info on cw_print_info.prime_print_info_id=cw_print_design.print_design_for where print_design_for = "'.$print_doc_id.'" and cw_print_info.trans_status = 1';
$design_data = $this->db->query("CALL sp_a_run ('SELECT','$design_qry')");
$design_result = $design_data->result();
$design_data->next_result();
$print_design = $design_result[0]->print_design;
$print_type = $design_result[0]->print_type;
if((int)$print_type === 4){
$style = '';
}else{
$style = "<style>
table{
border: 1px !important;
border-collapse: collapse !important;
empty-cells: show !important;
max-width: 100% !important;
font-size: 13px !important;
}
tbody {
border: 1px !important;
border-collapse: collapse !important;
empty-cells: show !important;
max-width: 100% !important;
font-size: 13px !important;
}
td, th {
border: 1px solid #000 !important;
font-size: 13px !important;
}
td.fr-thick,th.fr-thick {
border-width: 2px !important;
}
table.fr-dashed-borders td, table.fr-dashed-borders th {
border-style: dashed !important;
}
</style>";
}
$print_design = $style."".$print_design;
$print_design = str_replace('~','"',$print_design);
$block_qry = 'select * from cw_print_block where print_block_for = "'.$print_doc_id.'" and trans_status = 1';
$block_data = $this->db->query("CALL sp_a_run ('SELECT','$block_qry')");
$block_result = $block_data->result();
$block_data->next_result();
foreach($block_result as $block){
$prime_print_block_id = $block->prime_print_block_id;
$print_block_name = $block->print_block_name;
$print_block_type = (int)$block->print_block_type;
$print_block_table = $block->print_block_table;
$print_block_column = $block->print_block_column;
$suppressed_data = $block->suppressed_data;
$cumulative_data = $block->cumulative_data;
$table_qry = 'select * from cw_print_table where print_table_for_id = "'.$prime_print_block_id.'" and trans_status = 1';
$table_data = $this->db->query("CALL sp_a_run ('SELECT','$table_qry')");
$table_result = $table_data->result();
$table_data->next_result();
$line_table_query = "";
$cutome_table_check = array('transactions'=>'cw_transactions','custom_employees'=>'cw_custom_employees');
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;
$module_name = str_replace("cw_","",$line_prime_table);
$prime_id = "prime_".$module_name."_id";
$join_module_name = str_replace("cw_","",$line_join_table);
$join_prime_id = "prime_".$join_module_name."_id";
if((int)$line_sort === 1){
if($cutome_table_check[$module_name]){
$line_prime_table = " $line_prime_table ";
}
if($cutome_table_check[$join_module_name]){
$line_join_table = " $line_join_table on $line_join_col = $line_prime_col";
}
$line_table_query .= " $line_prime_table $line_join_type join $line_join_table";
}else{
if($cutome_table_check[$join_module_name]){
$line_table_query .= " $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_table_query){
$module_name = str_replace("cw_","",$print_block_table);
$prime_id = "prime_".$module_name."_id";
$line_table_query = " $print_block_table ";
}
if(!$print_block_column){
$print_block_column = "*";
}else{
$select_query = "";
$select_ytd_query = "";
$pick_query = "";
$map_column = explode(",",$print_block_column ?? "");
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$table_name = $map_column[0];
$column = $map_column[1];
$control_name = str_replace('cw_',"",$table_name);
if(($control_name === "transactions")||($control_name === "custom_employees")){
$control_name = "employees";
}
$form_qry = 'select prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "'.$control_name.'" and label_name = "'.$column.'" and trans_status = "1"';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$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_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else
if(($field_type === 2) || ($field_type === 3)){
$label_ytd = $label_name."_ytd";
$select_ytd_query .= "sum($table_name.$label_name) as $label_ytd, ";
$select_query .= "$table_name.$label_name , ";
}else{
$select_query .= "$table_name.$label_name , ";
}
}
}
}
$where_trans = "";
$where_trans_info = explode(",",$print_block_table ?? "");
foreach($where_trans_info as $trans_info){
if($trans_info === "cw_transactions"){
$select_query .= "cw_transactions.transactions_month , ";
}
$where_trans .= "$trans_info.trans_status = 1 and ";
}
$where_trans = rtrim($where_trans,'and ');
$where_qry = 'select * from cw_print_table_where where where_for_id = "'.$prime_print_block_id.'" and trans_status = 1';
$where_data = $this->db->query("CALL sp_a_run ('SELECT','$where_qry')");
$where_result = $where_data->result();
$where_data->next_result();
$where_condition = "";
if($where_result){
$where_condition = str_replace('^','"',$where_result[0]->where_condition);
$where_condition = str_replace('@logged_id@',$view_id,$where_condition);
$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 = 1 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);
}
}
}
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
if((int)$cumulative_data === 1){
$start_fin_date = $this->financial_info[0]->start_date;
$start_fin_date = date('m-Y',strtotime($start_fin_date));
$end_fin_date = $this->financial_info[0]->end_date;
$end_fin_date = date('m-Y',strtotime($end_fin_date));
$select_ytd_query = rtrim($select_ytd_query,',');
$select_ytd_query = rtrim($select_ytd_query,' , ');
$where_ytd_condition = ' and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") >= date_format(str_to_date("'.$start_fin_date.'", "%m-%Y"), "%Y-%m") and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") <= date_format(str_to_date("'.$end_fin_date.'", "%m-%Y"), "%Y-%m")';
$final_ytd_qry = "select $select_ytd_query from $line_table_query $pick_query where $where_trans $where_condition $where_ytd_condition";
$final_ytd_data = $this->db->query("CALL sp_a_run ('SELECT','$final_ytd_qry')");
$final_ytd_result = $final_ytd_data->result();
$final_ytd_data->next_result();
foreach($final_ytd_result as $ytd_rslt){
$map_column = explode(",",$print_block_column ?? "");
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$ytd_column = $map_column[1]."_ytd";
$ytd_value = $ytd_rslt->$ytd_column;
$replace_ytd_val = "@".$ytd_column."@";
$print_design = str_replace($replace_ytd_val,$ytd_value,$print_design);
}
}
}
$final_qry = "select $select_query from ".$line_table_query." $pick_query where $where_trans $where_condition";
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
$tr_line = "";
$th_line = "";
$count = 0;
$assign_date_formate_list = array("DMY"=>"d-m-Y","YMD"=>"Y-m-d","DFY"=>"d F Y","MY"=>"F-Y","YM"=>"Y-F","D"=>"d","M"=>"M","Y"=>"Y");
$split_qry = 'select * from cw_print_split where trans_status = 1 and split_table_info ="'.$print_doc_id.'"';
$split_data = $this->db->query("CALL sp_a_run ('SELECT','$split_qry')");
$split_result = $split_data->result();
$split_data->next_result();
$split_array = array();
foreach($split_result as $split){
$split_info = $split->split_info;
$split_colum = $split->split_colum;
$split_array[$split_colum] = $split_info;
}
if($final_result){
$data['print_sts'] = true;
foreach($final_result as $rslt){
$count++;
$map_column = explode(",",$print_block_column ?? "");
$td_line = "";
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$column = $map_column[1];
$value = $rslt->$column;
$replace_val = "@".$column."@";
//amount number is changed to in words for net pays--07SEP2019
if($column == 'net_pay'){
$value = $rslt->$column;
$print_design = str_replace($replace_val,$value,$print_design);
$net_pay_val = $value;
$net_pay_words = $this->numbertowords($net_pay_val);
$net_pay_words = strtoupper($net_pay_words);
$print_design = str_replace("@net_pay_words@",$net_pay_words,$print_design);
}else
if($column == 'employee_name'){
$value = ucwords($rslt->$column);
$print_design = str_replace($replace_val,$value,$print_design);
}else
if($column == 'reporting_person'){
$value = ucwords($rslt->$column);
$print_design = str_replace($replace_val,$value,$print_design);
}else
if($column == 'salary'){
$value = $rslt->$column;
$print_design = str_replace($replace_val,$value,$print_design);
$salary_val = $value;
$salary_words = $this->numbertowords($salary_val);
$salary_words = ucwords($salary_words);
$print_design = str_replace("@salary_words@",$salary_words,$print_design);
}
if($split_array[$replace_val]){
//Process split informtion
$process_function = $split_array[$replace_val];
if((int)$process_function === 1){
$transactions_month = $final_result[0]->transactions_month;
$employee_code = $final_result[0]->employee_code;
$loan_info = $this->get_loan_value($transactions_month,$employee_code);
$print_design = str_replace($replace_val,$loan_info,$print_design);
}
}else{
if($print_block_type === 1){
$print_design = str_replace($replace_val,$value,$print_design);
foreach($assign_date_formate_list as $key=>$formate){
if($column == 'transactions_month'){//transactions month static updated
$start = "@".$key."_";
$end = "_".$key."@";
$replace_val = $start.$column.$end;
$value = date('Y-m-d',strtotime("01-".$rslt->$column));
$date_value = date_create($value);
$replace_value = strtolower(date_format($date_value,$formate));
$print_design = str_replace($replace_val,$replace_value,$print_design);
}else{//not static month updated
$start = "@".$key."_";
$end = "_".$key."@";
$replace_val = $start.$column.$end;
$replace_val = $start.$column.$end;
$date_value = date_create($value);
$replace_value = date_format($date_value,$formate);
$print_design = str_replace($replace_val,$replace_value,$print_design);
}
}
}else
if($print_block_type === 2){
$td_line .= "<td style='text-align:center;'>$value</td>";
}
if($count === 1){
$head_name = ucwords(str_replace("_"," ",$column));
$th_line .= "<th style='text-align:center;'>$head_name</th>";
}
}
}
if($print_block_type === 2){
if($count === 1){
$th_line = "$th_line";
}
$tr_line .= "<tr>$td_line</tr>";
}
}
if($print_block_type === 2){
$table_list = "<table style='width:100%;'><thead>$th_line</thead><tbody>$tr_line</tbody></table>";
$replce_block = "@".strtolower(str_replace(" ","_",$print_block_name))."@";
$print_design = str_replace($replce_block,$table_list,$print_design);
}
}
$data['suppressed_data'] = $suppressed_data;
}
$print_design = str_replace("<br>","",$print_design);
$data['print_design'] = $print_design;
return $data;
}
//email data print
public function load_email_data($email_doc_id,$view_id){
$data['print_sts'] = false;
$design_qry = 'select email_design,print_type from cw_email_design inner join cw_print_info on cw_print_info.prime_print_info_id=cw_email_design.email_design_for where email_design_for = "'.$email_doc_id.'" and cw_print_info.trans_status = 1';
$design_data = $this->db->query("CALL sp_a_run ('SELECT','$design_qry')");
$design_result = $design_data->result();
$design_data->next_result();
$print_design = $design_result[0]->email_design;
$print_type = $design_result[0]->print_type;
if((int)$print_type === 4){
$style = '';
}else{
$style = "<style>
table{
border: 1px !important;
border-collapse: collapse !important;
empty-cells: show !important;
max-width: 100% !important;
font-size: 13px !important;
}
tbody {
border: 1px !important;
border-collapse: collapse !important;
empty-cells: show !important;
max-width: 100% !important;
font-size: 13px !important;
}
td, th {
border: 1px solid #000 !important;
font-size: 13px !important;
}
td.fr-thick,th.fr-thick {
border-width: 2px !important;
}
table.fr-dashed-borders td, table.fr-dashed-borders th {
border-style: dashed !important;
}
</style>";
}
$print_design = $style."".$print_design;
$print_design = str_replace('~','"',$print_design);
$block_qry = 'select * from cw_print_block where print_block_for = "'.$email_doc_id.'" and trans_status = 1';
$block_data = $this->db->query("CALL sp_a_run ('SELECT','$block_qry')");
$block_result = $block_data->result();
$block_data->next_result();
foreach($block_result as $block){
$prime_print_block_id = $block->prime_print_block_id;
$print_block_name = $block->print_block_name;
$print_block_type = (int)$block->print_block_type;
$print_block_table = $block->print_block_table;
$print_block_column = $block->print_block_column;
$suppressed_data = $block->suppressed_data;
$cumulative_data = $block->cumulative_data;
$table_qry = 'select * from cw_print_table where print_table_for_id = "'.$prime_print_block_id.'" and trans_status = 1';
$table_data = $this->db->query("CALL sp_a_run ('SELECT','$table_qry')");
$table_result = $table_data->result();
$table_data->next_result();
$line_table_query = "";
$cutome_table_check = array('transactions'=>'cw_transactions','custom_employees'=>'cw_custom_employees');
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;
$module_name = str_replace("cw_","",$line_prime_table);
$prime_id = "prime_".$module_name."_id";
$join_module_name = str_replace("cw_","",$line_join_table);
$join_prime_id = "prime_".$join_module_name."_id";
if((int)$line_sort === 1){
if($cutome_table_check[$module_name]){
$line_prime_table = " $line_prime_table ";
}
if($cutome_table_check[$join_module_name]){
$line_join_table = " $line_join_table on $line_join_col = $line_prime_col";
}
$line_table_query .= " $line_prime_table $line_join_type join $line_join_table";
}else{
if($cutome_table_check[$join_module_name]){
$line_table_query .= " $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_table_query){
$module_name = str_replace("cw_","",$print_block_table);
$prime_id = "prime_".$module_name."_id";
$line_table_query = " $print_block_table ";
}
if(!$print_block_column){
$print_block_column = "*";
}else{
$select_query = "";
$select_ytd_query = "";
$pick_query = "";
$map_column = explode(",",$print_block_column ?? "");
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$table_name = $map_column[0];
$column = $map_column[1];
$control_name = str_replace('cw_',"",$table_name);
if(($control_name === "transactions")||($control_name === "custom_employees")){
$control_name = "employees";
}
$form_qry = 'select prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "'.$control_name.'" and label_name = "'.$column.'" and trans_status = "1"';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$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_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else
if(($field_type === 2) || ($field_type === 3)){
$label_ytd = $label_name."_ytd";
$select_ytd_query .= "sum($table_name.$label_name) as $label_ytd, ";
$select_query .= "$table_name.$label_name , ";
}else{
$select_query .= "$table_name.$label_name , ";
}
}
}
}
$where_trans = "";
$where_trans_info = explode(",",$print_block_table ?? "");
foreach($where_trans_info as $trans_info){
if($trans_info === "cw_transactions"){
$select_query .= "cw_transactions.transactions_month , ";
}
$where_trans .= "$trans_info.trans_status = 1 and ";
}
$where_trans = rtrim($where_trans,'and ');
$where_qry = 'select * from cw_print_table_where where where_for_id = "'.$prime_print_block_id.'" and trans_status = 1';
$where_data = $this->db->query("CALL sp_a_run ('SELECT','$where_qry')");
$where_result = $where_data->result();
$where_data->next_result();
$where_condition = "";
if($where_result){
$where_condition = str_replace('^','"',$where_result[0]->where_condition);
$where_condition = str_replace('@logged_id@',$view_id,$where_condition);
$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 = 1 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);
}
}
}
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
if((int)$cumulative_data === 1){
$start_fin_date = $this->financial_info[0]->start_date;
$start_fin_date = date('m-Y',strtotime($start_fin_date));
$end_fin_date = $this->financial_info[0]->end_date;
$end_fin_date = date('m-Y',strtotime($end_fin_date));
$select_ytd_query = rtrim($select_ytd_query,',');
$select_ytd_query = rtrim($select_ytd_query,' , ');
$where_ytd_condition = ' and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") >= date_format(str_to_date("'.$start_fin_date.'", "%m-%Y"), "%Y-%m") and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") <= date_format(str_to_date("'.$end_fin_date.'", "%m-%Y"), "%Y-%m")';
$final_ytd_qry = "select $select_ytd_query from $line_table_query $pick_query where $where_trans $where_condition $where_ytd_condition";
$final_ytd_data = $this->db->query("CALL sp_a_run ('SELECT','$final_ytd_qry')");
$final_ytd_result = $final_ytd_data->result();
$final_ytd_data->next_result();
foreach($final_ytd_result as $ytd_rslt){
$map_column = explode(",",$print_block_column ?? "");
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$ytd_column = $map_column[1]."_ytd";
$ytd_value = $ytd_rslt->$ytd_column;
$replace_ytd_val = "@".$ytd_column."@";
$print_design = str_replace($replace_ytd_val,$ytd_value,$print_design);
}
}
}
$final_qry = "select $select_query from ".$line_table_query." $pick_query where $where_trans $where_condition";
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
$tr_line = "";
$th_line = "";
$count = 0;
$assign_date_formate_list = array("DMY"=>"d-m-Y","YMD"=>"Y-m-d","DFY"=>"d F Y","MY"=>"F-Y","YM"=>"Y-F","D"=>"d","M"=>"M","Y"=>"Y");
$split_qry = 'select * from cw_print_split where trans_status = 1 and split_table_info ="'.$print_doc_id.'"';
$split_data = $this->db->query("CALL sp_a_run ('SELECT','$split_qry')");
$split_result = $split_data->result();
$split_data->next_result();
$split_array = array();
foreach($split_result as $split){
$split_info = $split->split_info;
$split_colum = $split->split_colum;
$split_array[$split_colum] = $split_info;
}
if($final_result){
$data['print_sts'] = true;
foreach($final_result as $rslt){
$count++;
$map_column = explode(",",$print_block_column ?? "");
$td_line = "";
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$column = $map_column[1];
$value = $rslt->$column;
$replace_val = "@".$column."@";
//amount number is changed to in words for net pays--07SEP2019
if($column == 'net_pay'){
$value = $rslt->$column;
$print_design = str_replace($replace_val,$value,$print_design);
$net_pay_val = $value;
$net_pay_words = $this->numbertowords($net_pay_val);
$net_pay_words = strtoupper($net_pay_words);
$print_design = str_replace("@net_pay_words@",$net_pay_words,$print_design);
}else
if($column == 'employee_name'){
$value = ucwords($rslt->$column);
$print_design = str_replace($replace_val,$value,$print_design);
}else
if($column == 'reporting_person'){
$value = ucwords($rslt->$column);
$print_design = str_replace($replace_val,$value,$print_design);
}else
if($column == 'salary'){
$value = $rslt->$column;
$print_design = str_replace($replace_val,$value,$print_design);
$salary_val = $value;
$salary_words = $this->numbertowords($salary_val);
$salary_words = ucwords($salary_words);
$print_design = str_replace("@salary_words@",$salary_words,$print_design);
}
if($split_array[$replace_val]){
//Process split informtion
$process_function = $split_array[$replace_val];
if((int)$process_function === 1){
$transactions_month = $final_result[0]->transactions_month;
$employee_code = $final_result[0]->employee_code;
$loan_info = $this->get_loan_value($transactions_month,$employee_code);
$print_design = str_replace($replace_val,$loan_info,$print_design);
}
}else{
if($print_block_type === 1){
$print_design = str_replace($replace_val,$value,$print_design);
foreach($assign_date_formate_list as $key=>$formate){
if($column == 'transactions_month'){//transactions month static updated
$start = "@".$key."_";
$end = "_".$key."@";
$replace_val = $start.$column.$end;
$value = date('Y-m-d',strtotime("01-".$rslt->$column));
$date_value = date_create($value);
$replace_value = strtolower(date_format($date_value,$formate));
$print_design = str_replace($replace_val,$replace_value,$print_design);
}else{//not static month updated
$start = "@".$key."_";
$end = "_".$key."@";
$replace_val = $start.$column.$end;
$replace_val = $start.$column.$end;
$date_value = date_create($value);
$replace_value = date_format($date_value,$formate);
$print_design = str_replace($replace_val,$replace_value,$print_design);
}
}
}else
if($print_block_type === 2){
$td_line .= "<td style='text-align:center;'>$value</td>";
}
if($count === 1){
$head_name = ucwords(str_replace("_"," ",$column));
$th_line .= "<th style='text-align:center;'>$head_name</th>";
}
}
}
if($print_block_type === 2){
if($count === 1){
$th_line = "$th_line";
}
$tr_line .= "<tr>$td_line</tr>";
}
}
if($print_block_type === 2){
$table_list = "<table style='width:100%;'><thead>$th_line</thead><tbody>$tr_line</tbody></table>";
$replce_block = "@".strtolower(str_replace(" ","_",$print_block_name))."@";
$print_design = str_replace($replce_block,$table_list,$print_design);
}
}
$data['suppressed_data'] = $suppressed_data;
}
$print_design = str_replace("<br>","",$print_design);
$data['print_design'] = $print_design;
return $data;
}
// GENERATE PDF
public function generate_pdf($table_data,$category,$emp_num,$controller_name){
$category_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT category_name FROM `cw_category` where trans_status = 1 and prime_category_id = $category')");
$category_result = $category_info->result();
$category_info->next_result();
$category_name = $category_result[0]->category_name;
$category_name = strtolower(str_replace(" ","_",$category_name));
// Load pdf library
$this->load->library('pdf');
// Load HTML content
$this->dompdf->loadHtml($table_data);
// (Optional) Setup the paper size and orientation
$this->dompdf->setPaper('A4', 'portrait');
// Render the HTML as PDF
$this->dompdf->render();
// Output the generated PDF (1 = download and 0 = preview)
$output = $this->dompdf->output();
$folder = $controller_name;
$folder1 = $controller_name."_".$category_name;
$pdf_name = $emp_num;
$final_html = $output;
$oldmask = umask(0);
if(!file_exists($folder)){
mkdir($folder, 0777, true);
}
if(!file_exists($folder."/".$folder1)){
mkdir($folder."/".$folder1, 0777, true);
}
//Check File Exist
if(file_exists($folder."/".$folder1."/".$pdf_name.".pdf")){
chmod($folder."/".$folder1."/".$pdf_name.".pdf", 0777);
unlink($folder."/".$folder1."/".$pdf_name.".pdf");
}
file_put_contents($folder."/".$folder1."/".$pdf_name.".pdf" , $final_html);
chmod($folder."/".$folder1."/".$pdf_name.".pdf", 0777);
umask($oldmask);
$path = $folder."/".$folder1."/".$pdf_name.".pdf";
chmod($path, 0777, true);
return $path;
}
//number to words changed in payslip
public function numbertowords($number){
$no = round($number);
$point = round($number - $no, 2) * 100;
$hundred = null;
$digits_1 = strlen($no);
$i = 0;
$str = array();
$words = array('0' => '', '1' => 'One', '2' => 'Two',
'3' => 'Three', '4' => 'Four', '5' => 'Five', '6' => 'Six',
'7' => 'Seven', '8' => 'Eight', '9' => 'Nine',
'10' => 'Ten', '11' => 'Eleven', '12' => 'Twelve',
'13' => 'Thirteen', '14' => 'Fourteen',
'15' => 'Fifteen', '16' => 'Sixteen', '17' => 'Seventeen',
'18' => 'Eighteen', '19' =>'Nineteen', '20' => 'Twenty',
'30' => 'Thirty', '40' => 'Forty', '50' => 'Fifty',
'60' => 'Sixty', '70' => 'Seventy',
'80' => 'Eighty', '90' => 'Ninety');
$digits = array('', 'Hundred', 'Thousand', 'Lakh', 'Crore');
while ($i < $digits_1) {
$divider = ($i == 2) ? 10 : 100;
$number = floor($no % $divider);
$no = floor($no / $divider);
$i += ($divider == 10) ? 1 : 2;
if ($number) {
$plural = (($counter = count($str ?? [])) && $number > 9) ? '' : null;
$hundred = ($counter == 1 && $str[0]) ? ' and ' : null;
$str [] = ($number < 21) ? $words[$number] .
" " . $digits[$counter] . $plural . " " . $hundred
:
$words[floor($number / 10) * 10]
. " " . $words[$number % 10] . " "
. $digits[$counter] . $plural . " " . $hundred;
} else $str[] = null;
}
$str = array_reverse($str);
$result = implode('', $str ?? []);
$points = ($point) ? "." . $words[$point / 10]. " ".$words[$point = $point % 10] : '';
return $result;
}
public function dynamic_mail_sent($form_id,$pick_array,$previous_data){
$arraycompare = array_diff_assoc($pick_array ?? [],$previous_data ?? []);
$column_data_qry = 'select cw_email_info.table_values,cw_email_info.query_list_id from cw_email_info where cw_email_info.email_info_module_id = "custom_approval" and cw_email_info.trans_status = 1';
$column_data_info = $this->db->query("CALL sp_a_run ('SELECT','$column_data_qry')");
$column_data_rlst = $column_data_info->result_array();
$column_data_info->next_result();
$column_data_rlst = array_reduce($column_data_rlst, function($result, $arr){
$result[$arr['query_list_id']][] = $arr['table_values'];
return $result;
}, array());
// Keep only hr_status key with its value _ARN 15-10-2025
if($this->config->item("db_name") === 'aeq_hrms_db'){
if(isset($arraycompare['hr_status']) && $arraycompare['hr_status'] == 3){
$column_data_rlst = array('hr_status' => $column_data_rlst['hr_status']);
}
}
$final_pick = array_intersect_key($arraycompare, $column_data_rlst);
$column = http_build_query($final_pick, '', '&');
$column_qry = "";
$mobile_no = "";
if($column){
$column = str_replace('=','" and table_values = "',$column);
$column = str_replace('&','") or (query_list_id = "',$column);
$column_qry = '(query_list_id = "'.$column.'")';
$mail_status = "";
//Email Info
$email_info_qry = 'select cw_email_info.email_info_name,cw_email_info.email_attach_column,cw_email_info.prime_email_info_id,email_design,email_subject from cw_email_info INNER JOIN cw_mail_design ON cw_mail_design.email_design_for = cw_email_info.prime_email_info_id where cw_email_info.email_info_module_id = "custom_approval" and cw_email_info.trans_status = 1 and '.$column_qry.'';
$email_data_info = $this->db->query("CALL sp_a_run ('SELECT','$email_info_qry')");
$email_info_rsLt = $email_data_info->result();
$email_data_info->next_result();
$column_data_qry = 'select email_table_email_column,cw_email_block.prime_email_block_id,email_block_for,email_block_type,email_block_table,email_block_column,email_name_table,email_table_column,email_table_email_column,email_table_list FROM cw_email_block INNER JOIN cw_email_table on cw_email_table.email_name_table = cw_email_block.prime_email_block_id WHERE cw_email_block.email_block_module_id = "custom_approval" and cw_email_block.trans_status = 1 and cw_email_table.trans_status = 1 order by cw_email_block.email_block_type';
$column_data_info = $this->db->query("CALL sp_a_run ('SELECT','$column_data_qry')");
$column_data_rsLt = $column_data_info->result_array();
$column_data_info->next_result();
$email_block_rsLt = array_reduce($column_data_rsLt, function($result, $arr){
$result[$arr['email_block_for']][] = $arr;
return $result;
}, array());
$where_qry = 'select where_condition,where_for_id from cw_email_table_where where trans_status = 1';
$where_data = $this->db->query("CALL sp_a_run ('SELECT','$where_qry')");
$where_result = $where_data->result_array();
$where_data->next_result();
$where_result = array_reduce($where_result, function($result, $arr){
$result[$arr['where_for_id']] = $arr;
return $result;
}, array());
$table_join_qry = 'select line_prime_table,line_prime_col,line_join_type,line_join_table,line_join_col,line_sort,email_table_for_id from cw_email_table_join where trans_status = 1';
$table_join_info = $this->db->query("CALL sp_a_run ('SELECT','$table_join_qry')");
$table_join_rlst = $table_join_info->result_array();
$table_join_info->next_result();
$table_join_rlst = array_reduce($table_join_rlst, function($result, $arr){
$result[$arr['email_table_for_id']][] = $arr;
return $result;
}, array());
$table_content_join_qry = 'select line_prime_table_content,line_prime_col_content,line_join_type_content,line_join_table_content,line_join_col_content,line_sort_content,email_content_table_for_id from cw_email_content_table_join where trans_status = 1';
$table_content_join_info = $this->db->query("CALL sp_a_run ('SELECT','$table_content_join_qry')");
$table_content_join_rslt = $table_content_join_info->result_array();
$table_content_join_info->next_result();
$table_content_join_rslt = array_reduce($table_content_join_rslt, function($result, $arr){
$result[$arr['email_content_table_for_id']][] = $arr;
return $result;
}, array());
$form_qry = 'select prime_module_id,prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where trans_status = "1"';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result_array();
$form_data->next_result();
$form_result = array_reduce($form_result, function($result, $arr){
$result[$arr['prime_module_id']][$arr['label_name']][] = $arr;
return $result;
}, array());
$session_query = 'select session_value from cw_session_value where session_for = 1 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();
$config_query = 'SELECT smtp_server,sender_name,bcc,port_no,sender_email,mail_username,mail_password,connection_type FROM cw_mail_configurations WHERE mail_status = 1 and trans_status = 1';
$config_info = $this->db->query("CALL sp_a_run ('SELECT','$config_query')");
$config_result = $config_info->result();
$config_info->next_result();
$attachment_qry = 'SELECT prime_print_design_id,print_design FROM cw_print_design WHERE trans_status = 1';
$attachment_info = $this->db->query("CALL sp_a_run ('SELECT','$attachment_qry')");
$attachment_rlst = $attachment_info->result_array();
$attachment_info->next_result();
$attachment_rlst = array_reduce($attachment_rlst, function($result, $arr){
$result[$arr['prime_print_design_id']] = $arr;
return $result;
}, array());
$get_to_mail = "";
$cc_mail_column="";
foreach ($email_info_rsLt as $key => $value){
$prime_email_info_id = $value->prime_email_info_id;
$email_subject = $value->email_subject;
$email_design = $value->email_design;
$email_attach_column = $value->email_attach_column;
$pdf_name = $value->email_info_name;
$attachment = $attachment_rlst[$email_attach_column]['print_design'];
$email_block_rslts = $email_block_rsLt[$prime_email_info_id];
foreach ($email_block_rslts as $key => $value){
$email_block_id = $value['prime_email_block_id'];
$email_block_for = $value['email_block_for'];
$email_block_type = $value['email_block_type'];
$email_block_table = $value['email_block_table'];
$email_block_column = $value['email_block_column'];
$select_qry_column = $value['email_table_column'];
$select_attach_col = $value['email_table_attach'];
$email_column = $value['email_table_email_column'];
$mail_status_name = $value['email_block_name'];
$bcc_mail = $value['bcc_mail'];
if((int)$email_block_type === 1){
$where_prime_id = "cw_custom_employees.prime_custom_employees_id";
$prime_select_column = $select_qry_column;
}
$table_join_result = $table_join_rlst[$email_block_id];
$table_content_join_result = $table_content_join_rslt[$email_block_id];
$line_table_query = "";
foreach ($table_join_result as $join_key => $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'];
if((int)$line_sort === 1){
$line_prime_table = " $line_prime_table ";
$line_join_table = " $line_join_table on $line_join_col = $line_prime_col ";
$line_table_query .= " $line_prime_table $line_join_type join $line_join_table";
}else{
$line_table_query .= " $line_join_type join $line_join_table on $line_join_col = $line_prime_col ";
}
}
$line_table_content_query = "";
foreach ($table_content_join_result as $join_key => $table){
$line_prime_table_content = $table['line_prime_table_content'];
$line_prime_col_content = $table['line_prime_col_content'];
$line_join_type_content = $table['line_join_type_content'];
$line_join_table_content = $table['line_join_table_content'];
$line_join_col_content = $table['line_join_col_content'];
$line_sort_content = $table['line_sort_content'];
if((int)$line_sort === 1){
$line_prime_table_content = " $line_prime_table_content ";
$line_join_table_content = " $line_join_table_content on $line_join_col_content = $line_prime_col_content ";
$line_table_content_query .= " $line_prime_table_content $line_join_type_content join $line_join_table_content";
}else{
$line_table_content_query .= " $line_join_type_content join $line_join_table_content on $line_join_col_content = $line_prime_col_content ";
}
}
if(!$line_table_query){
$line_table_query = " $email_block_table ";
}
if(!$line_table_content_query){
$line_table_content_query = " $email_block_table ";
}
if(!$select_qry_column){
$select_qry_column = "*";
}else{
$select_query = "";
$pick_query = "";
$select_attach_column = "";
$map_column = explode(",",$select_qry_column ?? "");
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$table_name = $map_column[0];
$column = $map_column[1];
$control_name = str_replace('cw_',"",$table_name);
if($control_name === "custom_employees"){
$control_name = 'employees';
}
$form_result_array = $form_result[$control_name][$column];
foreach($form_result_array as $form){
$prime_form_id = (int)$form['prime_form_id'];
$view_name = $form['view_name'];
$label_name = $form['label_name'];
$field_type = (int)$form['field_type'];
$pick_list_type = (int)$form['pick_list_type'];
$pick_list = $form['pick_list'];
$pick_table = $form['pick_table'];
$auto_prime_id = $form['auto_prime_id'];
$auto_dispaly_value = $form['auto_dispaly_value'];
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
$select_attach_column .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$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_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$select_attach_column .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$select_attach_column .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$select_attach_column .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else
if(($field_type === 2) || ($field_type === 3)){
$label_ytd = $label_name."_ytd";
$select_query .= "$table_name.$label_name , ";
$select_attach_column .= "$table_name.$label_name , ";
}else{
$select_query .= "$table_name.$label_name , ";
$select_attach_column .= "$table_name.$label_name , ";
}
}
}
}
$where_trans = "";
$where_trans_info = explode(",",$email_block_table ?? "");
foreach($where_trans_info as $trans_info){
$where_trans .= "$trans_info.trans_status = 1 and ";
}
$where_trans = rtrim($where_trans,'and ');
$where_condition = "";
if($where_result){
$where_condition = str_replace('^','"',$where_result[$prime_email_block_id]['where_condition']);
$where_condition = str_replace('@logged_id@',$this->logged_id,$where_condition);
$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");
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);
}
}
}
$select_attach_column = rtrim($select_attach_column,' , ');
$email_column = 'CONCAT_WS(",",'.$email_column.') as mail_column';
if((int)$email_block_type === 1){
$prime_qry = "select $select_query$select_attach_column from ".$line_table_content_query." $pick_query where $where_prime_id = ".$form_id." and $where_trans $where_condition";
$prime_data = $this->db->query("CALL sp_a_run ('SELECT','$prime_qry')");
$prime_result = $prime_data->result();
$prime_data->next_result();
$email_qry = "select $email_column from ".$line_table_query." $pick_query where $where_prime_id = ".$form_id." and $where_trans $where_condition";
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_result = $email_data->result();
$email_data->next_result();
$get_to_mail = $email_result[0]->mail_column;
}else{
$email_qry = "select $email_column from ".$line_table_query." $pick_query where $where_prime_id = ".$form_id." and $where_trans $where_condition";
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_result = $email_data->result();
$email_data->next_result();
$bcc_email = $email_result[0]->mail_column;
$cc_mail_column .= $bcc_email.",";
$bcc_email_column = rtrim($cc_mail_column,',');
}
}
}
if($prime_result){
$assign_date_formate_list = array("DMY"=>"d-m-Y","YMD"=>"Y-m-d","DFY"=>"d F Y","MY"=>"F-Y","YM"=>"Y-F","D"=>"d","M"=>"M","Y"=>"Y");
$data['email_sts'] = true;
foreach($prime_result as $rslt){
$count++;
$map_column = explode(",",$prime_select_column ?? "");
$td_line = "";
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$column = $map_column[1];
$value = $rslt->$column;
$replace_val = "@".$column."@";
$email_design = str_replace($replace_val,$value,$email_design);
// TODAY DATE COMES WILL REPLACED AND EMAIL SUBJECT REPLACE ADD_ARN 14-10-2025
$today_date = date('d-m-Y');
$attachment = str_replace("@today_date@",$today_date,$attachment);
$attachment = str_replace($replace_val,$value,$attachment);
foreach($assign_date_formate_list as $key=>$formate){
$start = "@".$key."_";
$end = "_".$key."@";
$replace_val = $start.$column.$end;
$replace_val = $start.$column.$end;
if(strtotime($value)){
$date_value = date_create($value);
$replace_value = date_format($date_value,$formate);
}else{
$replace_value = $value;
}
$email_design = str_replace($replace_val,$replace_value,$email_design);
$attachment = str_replace($replace_val,$replace_value,$attachment);
}
}
}
}
if($attachment){
$get_attachment = $this->get_email_attachment($attachment,$email_subject,$pdf_name);
}
if($get_to_mail){
$mail_send_status = $this->send_mail_dynamic($mail_status_name,$get_to_mail,$config_result,$email_subject,$email_design,$bcc_mail,$get_attachment,$bcc_email_column);
}
}else{
$mail_send_status = "";
}
return $mail_send_status;
}
public function send_mail_dynamic($mail_status_name,$get_to_mail,$config_result,$email_subject,$email_design,$bcc_mail,$attachment,$bcc_email_column){
$smtp_server = $config_result[0]->smtp_server;
$sender_name = $config_result[0]->sender_name;
//$bcc = explode(",",$config_result[0]->bcc);
$port_no = $config_result[0]->port_no;
$sender_email = $config_result[0]->sender_email;
$username = $config_result[0]->mail_username;
$password = $config_result[0]->mail_password;
$email_layer = $config_result[0]->connection_type;
if($get_to_mail){
try{
$mail = new PHPMailer();
//$mail->SMTPDebug = 1;
$mail->IsSMTP();
$mail->Host = $smtp_server; // Your SMTP PArameter
$mail->Port = $port_no; // Your Outgoing Port
$mail->SMTPAuth = true; // This Must Be True
$mail->Username = $username; // Your Email Address
$mail->Password = $password; // Your Password
$mail->SMTPSecure = $email_layer; // Check Your Server's Connections for TLS or SSL
$mail->From = $sender_email;
$mail->FromName = $sender_name;
$mail_arr = explode(",",$get_to_mail ?? "");
if((int)count($mail_arr ?? []) > 1){
foreach ($mail_arr as $mail_key => $to_mail){
$mail->AddAddress($to_mail);
}
}else{
$mail->AddAddress($get_to_mail);
}
$bcc_mail = explode(",",$bcc_mail ?? "");
if((int)count($bcc_mail ?? []) > 0){
foreach ($bcc_mail as $bcc_key => $bcc_mails){
$mail->AddCC($bcc_mails);
}
}
$bcc_email_column = explode(",",$bcc_email_column ?? "");
if((int)count($bcc_email_column ?? []) > 0){
foreach ($bcc_email_column as $bccs_key => $bccs_mails){
$mail->AddCC($bccs_mails);
}
}
if(file_exists($attachment)){
$mail->addAttachment($attachment);
}
$mail->IsHTML(true);
$mail->Subject = $email_subject;
$mail->Body = $email_design;
if($mail->Send()){
$status = 1;
$msg = $mail_status_name." Mail Sent Successfully,";
}else{
$status = 0;
$msg = $mail_status_name." Mail Not Sent,";
echo 'Mailer error: ' . $mail->ErrorInfo;
}
//Attachment
}catch(phpmailerException $e){
$status = 0;
$msg = $mail_status_name." Mail Not Sent,";
}catch(Exception $e){
$status = 0;
$msg = $mail_status_name." Mail Not Sent,";
}
}else{
$msg = $mail_status_name." Mail Id Not Available,";
}
return $msg;
}
public function get_email_attachment($attachment,$email_subject,$pdf_name){
//echo "BSK $attachment,$email_subject,$pdf_name"; die;
$style = "<style>table td.fr-highlighted,.fr-view table th.fr-highlighted{border:1px solid black !important;vertical-align: text-top;} table tr { line-height: 20px !important; } table {border-collapse: collapse !important;page-break-inside: avoid !important;}table td{vertical-align: text-top !important;}#earnings td{height : 16px;font-size:12px;}#earnings th{border-bottom:1px solid black;font-size:12px;}#earnings first-th{width:45%;}#deductions td{height : 16px;font-size:12px;}#net_pay td{height : 16px;}</style>";
$attachment = $style."".$attachment;
$table_data = "<!DOCTYPE html><html> <body>".$attachment."</body></html>";
// PREG REPLACE ADDED FOR STYLE INLINE STYLE ENABLE _ARN 14-10-2025
$table_data = preg_replace('/=~(.*?)~/', '="$1"', $table_data);
// Load pdf library
$this->load->library('pdf');
// Load HTML content
$this->dompdf->loadHtml($table_data);
// Render the HTML as PDF
$this->dompdf->render();
// Output the generated PDF (1 = download and 0 = preview)
$output = $this->dompdf->output();
$conroler_name = $this->control_name;
$pdf_name = str_replace(' ', '_', $pdf_name);
//new permission changes
$folder = $conroler_name."/".$pdf_name;
$final_html = $output;
$oldmask = umask(0);
if (!file_exists($folder)){
mkdir($folder, 0777, true);
}
if (!file_exists($conroler_name."/".$pdf_name)){
mkdir($conroler_name."/".$pdf_name, 0777, true);
}
//Check File Exist
if(file_exists($conroler_name."/".$pdf_name.".pdf")){
chmod($conroler_name."/".$pdf_name.".pdf", 0777);
unlink($conroler_name."/".$pdf_name.".pdf");
}
file_put_contents($conroler_name."/".$pdf_name.".pdf" , $final_html);
chmod($conroler_name."/".$pdf_name.".pdf", 0777);
umask($oldmask);
$path = $conroler_name."/".$pdf_name.".pdf";
chmod($path, 0777, true);
return $path;
}
public function get_email_setting(){
if($this->control_name === "employees"){
$control_name = "custom_approval";
}else{
$control_name = $this->control_name;
}
$email_setting_qry = 'select count(*) as email_count from cw_modules where cw_modules.module_id = "'.$control_name.'" and cw_modules.email_setting = 1 and cw_modules.trans_status = 1';
$email_setting_info = $this->db->query("CALL sp_a_run ('SELECT','$email_setting_qry')");
$email_setting_result = $email_setting_info->result();
$email_setting_info->next_result();
$email_count = $email_setting_result[0]->email_count;
$this->email_count = $email_count;
}
public function company_info(){
$company = 'select * from cw_company_information where cw_company_information.trans_status = 1';
$comp_info = $this->db->query("CALL sp_a_run ('SELECT','$company')");
$comp_result = $comp_info->result();
$comp_info->next_result();
$this->company_info = $comp_result;
return $comp_result;
}
public function form_setting_data($prime_module_id){
$from_query = 'select label_name,view_name,field_type,mandatory_field from cw_form_setting where prime_module_id = "'.$prime_module_id.'" and field_show = "1" and trans_status = "1" ORDER BY input_for,field_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result_array();
$form_data->next_result();
$form_result = array_reduce($form_result, function ($result, $arr) {
$result[$arr['label_name']] = $arr;
return $result;
}, array());
return $form_result;
}
public function role_based_condition(){
$role_based_query = 'select * from cw_role_base_condition where role_module_id = "'.$this->control_name.'" and find_in_set("'.$this->logged_user_role.'",role_condition_for) and trans_status = 1';
$role_based_info = $this->db->query("CALL sp_a_run ('SELECT','$role_based_query')");
$role_based_result = $role_based_info->result();
$role_based_info->next_result();
$role_based_condition = array();
foreach($role_based_result as $key => $condition) {
$role_based_condition[$condition->user_condition_type] = $condition->input_columns;
}
$this->role_based_condition = $role_based_condition;
}
//dependent dropdown form function
public function dependent_data_set(){
$prime_module_id = $this->input->post("prime_module_id");
$depen_select_val = $this->input->post("depen_select");
$depen_pick_input = $this->input->post("depen_pick_input");
$table_column1 = $this->input->post("table_column1");
$table_column2 = $this->input->post("table_column2");
$label_name = $this->input->post("label_id");
$pick_table = $this->input->post("pick_table");
$pick_list = $this->input->post("pick_list");
$pick_display = $this->input->post("pick_display_val");
$picklist_table_qry = 'select pick_list,pick_table,prime_form_id,label_name from cw_form_setting where prime_module_id = "'.$prime_module_id.'" and label_name in ("'.$depen_pick_input.'","'.$label_name.'") and trans_status = 1';
$picklist_table_data = $this->db->query("CALL sp_a_run ('SELECT','$picklist_table_qry')");
$picklist_table_rslt = $picklist_table_data->result_array();
$picklist_table_data->next_result();
$picklist_table_rslt = array_reduce($picklist_table_rslt, function($result, $arr){
$result[$arr['label_name']] = $arr;
return $result;
}, array());
$depen_pick_table = $picklist_table_rslt[$depen_pick_input]['pick_table'];
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($pick_display){
$pick_list = "$pick_table.$pick_list_val_1,CONCAT_WS(\" - \", $pick_table.$pick_display) as $pick_list_val_2";
}
$where_condition = $this->get_role_based_picklist($picklist_table_rslt[$label_name]['prime_form_id'],$prime_module_id);
$pick_list = $pick_table.'.'.implode(",$pick_table.",explode(',', $pick_list ?? "") ?? []);
$pick_query = "select $pick_list from $pick_table inner join $depen_pick_table on $depen_pick_table.$table_column1 = $pick_table.$table_column2 where $depen_pick_table.$table_column1 = \"$depen_select_val\" and $pick_table.trans_status = 1 and $depen_pick_table.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();
if($pick_result){
$pick_key = array_column($pick_result ?? [], $pick_list_val_1);
$pick_val = array_column($pick_result ?? [], $pick_list_val_2);
$final_pick = array_combine( $pick_key ?? [], $pick_val ?? []);
}
$drop_list = "<option value=''>$label_name</option>";
foreach($final_pick as $key => $val){
$drop_list .= "<option value='$key'>$val</option>";
}
echo $drop_list;
}
//fetch data auto picklist
public function fetch_picklist(){
$depand_lab_val = implode('","', $this->input->post("depand_lab_val") ?? []);
$label_id = $this->input->post("label_id");
$label_value = $this->input->post("label_value");
$prime_form_id = $this->input->post("prime_form_id");
$form_query = 'select * from cw_form_setting where prime_form_id in ("'.$depand_lab_val.'","'.$prime_form_id.'")';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_query')");
$form_result = $form_data->result_array();
$form_data->next_result();
$form_result = array_reduce($form_result, function($result, $arr){
$result[$arr['label_name']] = $arr;
return $result;
}, array());
$form_result_arr = $form_result;
unset($form_result_arr[$label_id]);
$dropdown_list = array();
foreach ($form_result_arr as $key => $form_for) {
$field_type = $form_for['field_type'];
if((int)$field_type === 5){
$label_name = $form_for['label_name'];
$pick_list = $form_for['pick_list'];
$pick_table = $form_for['pick_table'];
$table_column1 = $form_for['table_column1'];
$table_column2 = $form_for['table_column2'];
$dependent_pick = $form_for['dependent_pick_input'];
$pick_list_arr = explode(',', $pick_list ?? "");
$pick_display = $form_for['pick_display_value'];
$pick_list_key = $pick_list_arr[0];
$pick_list_val = $pick_list_arr[1];
if($pick_display){
$pick_list = "$pick_table.$pick_list_key,CONCAT_WS(\" - \", $pick_table.$pick_display) as $pick_list_val";
}
$depen_pick_tbl = $form_result[$dependent_pick]['pick_table'];
$inner_join = ' inner join '.$depen_pick_tbl.' on '.$depen_pick_tbl.'.'.$table_column1.' = '.$pick_table.'.'.$table_column2.'';
$inner_where = ' and '.$depen_pick_tbl.'.trans_status = 1';
$where_condition = $this->get_role_based_picklist($this->control_name,$prime_form_id);
$select_qry = 'select '.$pick_list.' from '.$pick_table.' '.$inner_join.' where '.$pick_table.'.trans_status = 1 and '.$pick_table.'.'.$table_column2.' = "'.$label_value.'" '.$inner_where.' '.$where_condition.' ';
$select_info = $this->db->query("CALL sp_a_run ('SELECT','$select_qry')");
$select_rslt = $select_info->result();
$select_info->next_result();
$pick_key = array_column($select_rslt ?? [], $pick_list_key);
$pick_value = array_column($select_rslt ?? [], $pick_list_val);
$dropdown_list[$label_name] = array_combine($pick_key ?? [], $pick_value ?? []);
}
}
echo json_encode(array('success' => TRUE, 'message' => "Proceed..!",'dropdown_list'=>$dropdown_list));
}
//formula column label name coloring
public function coloring_label(){
$formula_qry = 'select * from cw_form_bind_input where input_cond_module_id = "'.$this->control_name.'" and trans_status = 1';
$formula_data = $this->db->query("CALL sp_a_run ('SELECT','$formula_qry')");
$formula_result = $formula_data->result();
$formula_data->next_result();
$this->formula_result = $formula_result;
}
public function suggest_auto_filter(){
$search_term = $this->input->post_get('term');
$prime_form_id = $this->input->get('prime_form_id');
$form_query = 'select * from cw_form_setting where prime_form_id = "'.$prime_form_id.'"';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_query')");
$form_result = $form_data->result();
$form_data->next_result();
$where_condition = $this->get_role_based_picklist($prime_form_id,$this->control_name);
if($form_result){
$pick_table = $form_result[0]->pick_table;
$pick_list = $form_result[0]->pick_list;
$auto_prime_id = $form_result[0]->auto_prime_id;
$auto_dispaly_value = $form_result[0]->auto_dispaly_value;
$auto_list = "CONCAT(".str_replace(",",'," - ",',$pick_list).") as auto_list";
$suggest_query = "select $auto_prime_id,$auto_dispaly_value,$auto_list from $pick_table where ";
$col_list = explode(",",$pick_list ?? "");
$where_query = "";
foreach($col_list as $col){
$search_term = str_replace('"',"~",$search_term);
$search_term = str_replace("'","`",$search_term);
$search_term = str_replace("&","^",$search_term);
$where_query .= $col.' like "'.$search_term.'%" or ';
}
$where_query = rtrim($where_query," or ");
$suggest_query .= $where_query;
$suggest_query .= $where_condition;
$suggest_data = $this->db->query("CALL sp_a_run ('SELECT','$suggest_query')");
$suggest_result = $suggest_data->result();
$suggest_data->next_result();
foreach($suggest_result as $result){
$suggest_prime_id = $result->$auto_prime_id;
$suggest_dispaly = $result->$auto_dispaly_value;
$suggest_dispaly = str_replace("~",'"',$suggest_dispaly);
$suggest_dispaly = str_replace("`","'",$suggest_dispaly);
$suggest_dispaly = str_replace("^","&",$suggest_dispaly);
$suggest_list = $result->auto_list;
$suggest_list = str_replace("~",'"',$suggest_list);
$suggest_list = str_replace("`","'",$suggest_list);
$suggest_list = str_replace("^","&",$suggest_list);
$suggestions[] = array('value' => $suggest_prime_id, 'label' => $suggest_list, 'display_name' => $suggest_dispaly);
}
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
//DEPENDENT PRIME ID ARRAY FOR ALL DYNAMIC MODULE START
public function get_depend_fun(){
$get_depend_arr = json_decode(json_encode($this->form_info),true);
$get_depend_prime_id = array_reduce($get_depend_arr, function($result, $arr){
$result[$arr['label_name']] = $arr;
return $result;
}, array());
return $get_depend_prime_id;
}
//depend_label_id autopick
public function depend_label_fun(){
$get_depend_arr = json_decode(json_encode($this->form_info),true);
$depend_label_id = array_reduce($get_depend_arr, function($result, $arr){
if((int)$arr['field_type'] === 9){
$result[$arr['label_name']] = $arr['dependent_pick_input'];
}
return $result;
}, array());
return $depend_label_id;
}
//prev view inputs
public function get_previous_view($form_id){
$control_name = "employees";
$table_name = "cw_".$control_name;
$prime_table_id = "prime_".$control_name."_id";
$column_query = 'select label_name,default_value from cw_form_setting where prime_module_id = "'.$control_name.'" and field_show = "1" and input_view_type in(1,2) and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) ORDER BY input_for,field_sort asc';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$column_query')");
$column_rslt = $column_info->result();
$column_info->next_result();
$column_name = array_column($column_rslt ?? [],'label_name');
$default_value = array_column($column_rslt ?? [],'default_value');
$form_result = array();
if($column_name){
if((int)$form_id === 0){
$form_result[0] = array_combine($column_name, $default_value);
}else{
$from_query = 'select '.implode(",",$column_name ?? []).' from '.$table_name.' where '.$table_name.'.trans_status = 1 and '.$table_name.'.'.$prime_table_id.' = '.$form_id.' ';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result_array();
$form_data->next_result();
}
}
return $form_result;
}
}
?>