File: //home/cafsindia/hrms_allyindian_com/application/controllers/Emp_doc_export.php
<?php if( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Emp_doc_export extends Action_controller{
public $form_data;
public function __construct(){
parent::__construct('emp_doc_export');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS -BY AR
public function index(){
//PAGE INFO FUNCTION
$this->page_info();
$doc_query = 'SELECT label_name, view_name FROM cw_form_setting WHERE field_show = 1 AND input_view_type in (1,2) AND prime_module_id = "employees" AND field_type = 10';
$doc_result = $this->db->query("CALL sp_a_run('SELECT', '$doc_query')");
$data = $doc_result->result();
$doc_result->next_result();
$document_dropdown[1] = 'All';
$count = 2;
foreach($data as $row){
$document_dropdown[$count] = $row->view_name;
$count++;
}
$data['encKey'] = $this->generateKey();
$data['document_field_option'] = $document_dropdown;
$data['table_head'] = $this->table_head;
$this->get_picker_list();
$data['form_info'] = $this->$form_data;
$data['all_pick'] = $this->pick_list;
$this->load->view("$this->control_name/manage",$data);
}
// GET PICKLISTS AND DATAS OF FORM_INFO
public function get_picker_list(){
$pro_qry = [];
$pro_qry[] = array("return"=>"form_info","qry"=>'select * from cw_form_setting where prime_module_id = "employees" and field_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and field_show = 1 and search_show = 1');
if(count($pro_qry) > 0){
$page_info_rslt = $this->run_multi_qry($pro_qry);
$pro_pick_list = [];
foreach($page_info_rslt->rslt->form_info as $key => $value){
$field_type = (int)$value->field_type;
$field_show = (int)$value->field_show;
if($field_show === 1){
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$pro_pick_list[$key] = $value;
}
}
}
if(count($pro_pick_list) > 0){
$this->get_pick_list_qry($pro_pick_list);
}
}
$this->$form_data = $page_info_rslt->rslt->form_info;
}
// AUTOCOMPLTE DYNAMICALLY BASED FIELD TYPE IN OUR FILED NAME BY -AR
public function dynamic_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);
}
$search_term = $this->input->post('term', true);
$type = $this->input->post('type', true);
$label = $this->input->post('label', true);
$suggestions = [];
$this->get_picker_list();
$form_info = $this->$form_data;
if($type == 'employee'){
$query = 'select employee_code from cw_employees where employee_code like "' . $search_term . '%"';
$result = $this->db->query("CALL sp_a_run('SELECT','$query')");
$data = $result->result();
$result->next_result();
foreach($data as $row){
$suggestions[] = ['value' => $row->employee_code, 'label' => $row->employee_code];
}
}else
if($type == 'dynamic'){
// Find field info for this label
$field_info = null;
foreach($form_info as $field){
if($field->label_name === $label){
$field_info = $field;
break;
}
}
if($field_info){
$pick_table = $field_info->pick_table;
$pick_cols = explode(',', $field_info->pick_list);
$pick_col_id = $pick_cols[0];
$pick_col_val = $pick_cols[1];
// Build raw SQL like your employee query
$query = 'SELECT '.$pick_col_id.' AS value, '.$pick_col_val.' AS label FROM '.$pick_table.'
WHERE trans_status = 1 AND '.$pick_col_val.' LIKE "'.$this->db->escape_like_str($search_term).'%"
LIMIT 20';
$result = $this->db->query("CALL sp_a_run('SELECT','$query')");
$data = $result->result();
$result->next_result();
foreach($data as $row){
$suggestions[] = ['value' => $row->value, 'label' => $row->label];
}
}else{
$suggestions[] = ['value' => '0', 'label' => 'Invalid field'];
}
}
else{
$suggestions[] = ['value' => '0', 'label' => 'Invalid type'];
}
if(empty($suggestions)){
$suggestions[] = ['value' => '0', 'label' => 'No data found for this search'];
}
echo json_encode($suggestions);
}
//SAVE MODEL DATA TO DATA BASE
public function save_emp_doc(){
$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);
}
$description = $this->input->post('description');
$filter_by = $this->input->post('filter_by');
$filter_data = $this->input->post('filter_data');
$created_on = date('Y-m-d H:i:s');
$trans_created_date = $created_on;
$trans_created_by = $this->session->userdata('logged_id');
$process_status = "0";
$fdata = json_decode($filter_data, true);
$where = "trans_status = 1 AND prime_employees_id != 1"; // Initialize $where properly
$this->get_picker_list();
$form_info = $this->$form_data;
if(isset($fdata['inactive']) && $fdata['inactive'] == 0 && $filter_by != 2){
$where .= " AND termination_status = 0";
}
if($filter_by == 1){
$list_no_field = ["emp_name", "employee_code", "aadhar_card_no", "termination_status"];
foreach($form_info as $field){
$label_id = $field->label_name;
$field_type = (int)$field->field_type;
// Skip excluded fields or empty/missing values
if(in_array($label_id, $list_no_field) || !isset($fdata[$label_id]) || $fdata[$label_id] === '' || $fdata[$label_id] === null){
continue;
}
$value = $fdata[$label_id];
if($field_type === 5 || $field_type === 7){
$values = is_array($value) ? $value : [$value]; // convert single value to array
$escaped_values = array_map(function($v){
return "'" . $this->db->escape_str($v) . "'";
}, $values);
if(!empty($escaped_values)){
$where .= " AND `$label_id` IN (" . implode(',', $escaped_values) . ")";
}
}
else
if($field_type === 9 && is_array($value)){
$keys = array_keys($value);
$keys = array_filter($keys, function($v){
return $v !== '' && $v !== null;
}); // remove empty
if(!empty($keys)){
$escaped_keys = array_map('intval', $keys);
$where .= " AND `$label_id` IN (" . implode(',', $escaped_keys) . ")";
}
}
}
}else
if($filter_by == 2){
if(!empty($fdata['employee_code'])){
$emp_code = $this->db->escape_like_str($fdata['employee_code']);
$where .= " AND employee_code = '" . $emp_code . "'";
}
}elseif($filter_by == 3 || $filter_by == 4){
if(!empty($fdata['start_date']) && !empty($fdata['end_date'])){
$start_date = $this->db->escape($fdata['start_date'] . ' 00:00:00');
$end_date = $this->db->escape($fdata['end_date'] . ' 23:59:59');
if($filter_by == 3){
$where .= " AND date_of_onboard BETWEEN $start_date AND $end_date";
}else{
$where .= " AND date_of_joining BETWEEN $start_date AND $end_date";
}
}
}
// Final employee count query
$emp_count_qry = "SELECT count(*) as emp_count FROM cw_employees WHERE $where";
$emp_count_info = $this->db->query("CALL sp_a_run('SELECT', " . $this->db->escape($emp_count_qry) . ")");
$emp_count_rslt = $emp_count_info->result();
$emp_count_info->next_result();
$emp_count = $emp_count_rslt[0]->emp_count;
if(!$emp_count){
echo json_encode(['success' => false, 'message' => 'Employees is inActive or No Data Found']);
exit ;
}
// Insert query
$escaped_description = $this->db->escape($description);
$escaped_filter_by = $this->db->escape($filter_by);
$escaped_filter_data = $this->db->escape($filter_data);
$escaped_created_on = $this->db->escape($created_on);
$escaped_created_by = $this->db->escape($trans_created_by);
$escaped_trans_date = $this->db->escape($trans_created_date);
$escaped_process_status = $this->db->escape($process_status);
$insert_qry = "INSERT INTO cw_emp_doc_export(description_data, filter_by, filter_data, total, completed, failed, process_status, created_on, trans_created_by, trans_created_date, trans_status)VALUES($escaped_description, $escaped_filter_by, $escaped_filter_data, $emp_count, 0, 0, $escaped_process_status, $escaped_created_on, $escaped_created_by, $escaped_trans_date, 1)";
$ins_info = $this->db->query("CALL sp_a_run('INSERT', " . $this->db->escape($insert_qry) . ")");
$ins_result = $ins_info->result();
$ins_info->next_result();
if($ins_result){
echo json_encode(['success' => true, 'message' => 'Data inserted successfully.']);
}else{
echo json_encode(['success' => false, 'message' => 'Failed to insert data.']);
}
}
//LOAD TABEL WITH FILTERS - BY AR
public function search(){
$dec_data = $this->cryptoDecrypt($_POST['Payload']);
$_POST = $dec_data['data'];
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....'));
exit(0);
}
//SEARCH INFO FUNCTION
$this->search_info();
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$search = $this->input->post('search');
$search = trim($search['value']);
$search_query = str_replace("@SELECT@",$this->select_query,$this->base_query);
$search_result = array();
//ADDED BASIC,FILTER,COMMON QUERY HERE
$common_search = "";
if($search){
foreach($this->table_head as $setting){
$field_type = (int)$setting->field_type;
$input_view_type = (int)$setting->input_view_type;
$label_id = strtolower(str_replace(" ","_",$setting->label_name));
$field_isdefault = (int)$setting->field_isdefault;
if($field_isdefault === 1){
if(($input_view_type === 1) ||($input_view_type === 2)){
$search_label = "$this->prime_table.$label_id";
$search_val = "";
if($field_type === 4){ // having issues in date search
if(strtotime($search)){
$search_val = date('Y-m-d',strtotime($search));
$common_search .= ' or '. $search_label .' like "'.$search_val.'%"';
}
}else
if(($field_type === 5) ||($field_type === 7) ||($field_type === 9)){
$result = array_filter($this->pick_list[$label_id]['array_list'], function($item) use($search){
if(stripos($item, $search) !== false){
return true;
}
return false;
});
if($result){
$pick_key = implode('", "', array_keys($result));
$common_search .= ' or '. $search_label .' in("'.$pick_key.'")';
}
}else{
$common_search .= ' or '. $search_label .' like "'.$search.'%"';
}
}
}
}
if($common_search){
$common_search = ltrim($common_search,' or ');
$common_search = " and($common_search)";
$common_search = str_replace("(,","(",$common_search);
$common_search = str_replace("()","(0)",$common_search);
}
}
$count_all_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query = $count_all_query.' where '.$this->prime_table.'.trans_status in (0,1) '.$role_condition.$fliter_query.$common_search;
$search_query .= " where $this->prime_table.trans_status in (0,1) $role_condition $fliter_query $common_search";
if(!$search){
$search_query .= " ORDER BY created_on DESC";
}
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_pro_qry = [];
$search_pro_qry[] = array("return"=>"total_count", "qry"=>$count_all_query);
$search_pro_qry[] = array("return"=>"filtered_count", "qry"=>$count_query);
$search_pro_qry[] = array("return"=>"search_result", "qry"=>$search_query);
$search_info_rslt = $this->run_multi_qry($search_pro_qry);
$total_count = $search_info_rslt->rslt->total_count[0]->allcount;
$filtered_count = $search_info_rslt->rslt->filtered_count[0]->allcount;
$search_result = json_decode(json_encode($search_info_rslt->rslt->search_result),true);
if($search_result === null || $search_result === ''){
$search_result = [];
}
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
// FAILED DATAS WILL GET BASED DOC EXPORT ID - BY AR
public function failed_log_search(){
$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);
}
$prime_id = $this->input->post('table_id'); // Input from AJAX
$failed_log_query = 'SELECT employee_code, employee_name,remarks FROM cw_doc_export_failed_log WHERE emp_doc_export_id = "' . $prime_id . '"';
// Execute using stored procedure pattern
$failed_log_info = $this->db->query("CALL sp_a_run('SELECT', '$failed_log_query')");
$failed_log_rslt = $failed_log_info->result_array();
$failed_log_info->next_result(); // flush result set for multiple queries
if(count($failed_log_rslt) > 0){
echo json_encode(array('success' => true,'message' => 'Failed log data loaded successfully.','pro_tab_create' => $failed_log_rslt));
}else{
echo json_encode(array('success' => false,'message' => 'No failed logs found.','pro_tab_create' => []));
}
}
}
?>