File: //home/cafsindia/hrms_allyindian_com/application_bk/controllers/Module_audit_log.php
<?php if( ! defined('BASEPATH')) exit('No direct script is allowed');
include_once('./application/libraries/xlsxwriter.class.php');
require_once("Action_controller.php");
class Module_audit_log extends Action_controller{
public function __construct(){
parent::__construct('module_audit_log');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//PAGE INFO FUNCTION
$this->page_info();
$data['encKey'] = $this->generateKey();
$data['module_sts'] =(int)$this->module_sts;
$data['quick_link'] = $this->quick_link;
$data['pick_list'] = $this->pick_list;
$data['form_info'] = $this->form_info;
$data['table_head'] = $this->table_head;
$data['fliter_list'] = $this->fliter_list;
$data['freeze_list'] = $this->freeze_list;
// MODULES DROPDOWN MAKED _ARN
$modules_qry = 'SELECT module_id,module_name FROM cw_modules WHERE show_module = 1 and trans_status = 1 and menu_id = 5 and sub_menu_id = 3 ';
$modules_info = $this->db->query("CALL sp_a_run('SELECT','$modules_qry')");
$modules_rslt = $modules_info->result();
$modules_info->next_result();
$modules_dropdown = array("" => "---- Select Module ----");
if(!empty($modules_rslt)){
foreach($modules_rslt as $row){
$modules_dropdown[$row->module_id] = $row->module_name;
}
}
$data['modules_dropdown'] = $modules_dropdown;
$this->load->view("$this->control_name/manage",$data);
}
// SEARCH FUNCTION FOR GET THE MODULE LOG DATAS
public function search(){
$dec_data = $this->cryptoDecrypt($_POST['Payload']);
$_POST = $dec_data['data'];
if(!$_POST){
echo json_encode(['success' => false,'message' => 'Invalid Request..']);
exit;
}
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$order = $this->input->post('order');
$order_col_data = $this->input->post('columns');
$search_input = $this->input->post('search');
$process_by =(int)$this->input->post('process_by');
$module_id = $this->input->post('module_id');
$emp_id = $this->input->post('emp_id');
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$emp_code = $this->input->post('emp_code');
$column_index = $order[0]['column'];
$order_dir = $order[0]['dir'];
$order_column = $order_col_data[$column_index]['data'];
$search_value = trim($search_input['value']);
// Module name replaced
$modules_qry = 'SELECT module_id,module_name FROM cw_modules WHERE show_module = 1 and trans_status = 1 and menu_id = 5 and sub_menu_id = 3';
$modules_info = $this->db->query("CALL sp_a_run('SELECT','$modules_qry')");
$modules_rslt = $modules_info->result();
$modules_info->next_result();
$module_map = [];
foreach($modules_rslt as $m){
$module_map[$m->module_id] = $m->module_name;
}
// Build WHERE condition safely
$where_qry = "";
$where_qry = " AND mal.module_name = ".$this->db->escape($module_id);
if($process_by === 1 && $emp_id){
$where_qry .= " AND mal.trans_created_by = ".$this->db->escape($emp_id);
}else
if($process_by === 2 && $from_date && $to_date){
$from_date = date('Y-m-d', strtotime($from_date));
$to_date = date('Y-m-d', strtotime($to_date));
$where_qry .= " AND mal.trans_created_date BETWEEN " . $this->db->escape($from_date . ' 00:00:00') .
" AND mal.trans_created_date AND " . $this->db->escape($to_date . ' 23:59:59');
}else
if($process_by === 3 && $emp_code){
$es_emp_code = $this->db->escape_str($emp_code); // safer escaping
$where_qry .= " AND FIND_IN_SET(".$this->db->escape($es_emp_code).", mal.employee_code)";
}
$main_query = "SELECT mal.prime_module_audit_log_id,mal.module_name,mal.filter_keys,mal.old_value,mal.new_value,mal.action_type, CONCAT(cw_employees.employee_code, ' - ', cw_employees.emp_name) AS created_by,mal.trans_created_date FROM cw_module_audit_log AS mal JOIN cw_employees ON cw_employees.prime_employees_id = mal.trans_created_by WHERE mal.trans_status = 1 AND intract_type = 'user' $where_qry";
// Search filter
if($search_value){
$search_value = $this->db->escape_like_str($search_value);
$main_query .= " AND (JSON_SEARCH(mal.filter_keys, 'all', '%{$search_value}%') IS NOT NULL OR
JSON_SEARCH(mal.old_value, 'all', '%{$search_value}%') IS NOT NULL OR JSON_SEARCH(mal.new_value, 'all', '%{$search_value}%') IS NOT NULL OR mal.filter_keys LIKE '%\"{$search_value}\"%' OR mal.old_value LIKE '%\"{$search_value}\"%' OR mal.new_value LIKE '%\"{$search_value}\"%')";
}
$main_query .= " ORDER BY mal.$order_column $order_dir";
if((int)$per_page !== -1){
$main_query .= " LIMIT $start, $per_page";
}
// Count queries
$count_all_query = "SELECT COUNT(*) AS allcount FROM cw_module_audit_log WHERE trans_status = 1 AND intract_type = 'user'";
$count_filtered_query = "SELECT COUNT(*) AS allcount FROM cw_module_audit_log AS mal JOIN cw_employees ON cw_employees.prime_employees_id = mal.trans_created_by WHERE mal.trans_status = 1 AND intract_type = 'user' $where_qry";
$multi_qry = [
["return" => "total_count", "qry" => $count_all_query],
["return" => "filtered_count", "qry" => $count_filtered_query],
["return" => "search_result", "qry" => $main_query],
];
$result = $this->run_multi_qry($multi_qry);
$total_count = $result->rslt->total_count[0]->allcount;
$filtered_count = $result->rslt->filtered_count[0]->allcount;
$search_result = json_decode(json_encode($result->rslt->search_result), true);
// GET EXCEL FORMAT DATA REPLACE ON SEARCH DATA
$excel_format_qry = "SELECT prime_excel_format_id, excel_name FROM cw_util_excel_format WHERE trans_status = 1";
$excel_format_rslt = $this->db->query($excel_format_qry)->result();
$excel_format_map = [];
foreach($excel_format_rslt as $ef){
$excel_format_map[$ef->prime_excel_format_id] = $ef->excel_name;
}
// GET PICKLIST MAPPING DATAS
$picklist_data = $this->get_picklist_mappings();
// THIS IS REPLACED THERE VALUE BASED ON OUR FIELD NAME KEYS
foreach($search_result as &$row){
$old_data = json_decode($row['old_value'], true);
$new_data = json_decode($row['new_value'], true);
$filter_keys = json_decode($row['filter_keys'], true);
// Replace picklist IDs with readable names
$old_data = $this->replace_picklist_values($old_data, $picklist_data);
$new_data = $this->replace_picklist_values($new_data, $picklist_data);
$filter_keys = $this->replace_picklist_values($filter_keys, $picklist_data);
if(isset($filter_keys['Excel Format'])){
$excel_id = $filter_keys['Excel Format'];
if(isset($excel_format_map[$excel_id])){
$filter_keys['Excel Format'] = $excel_format_map[$excel_id];
}
}
if(isset($filter_keys['Sheet Name']) && $filter_keys['Sheet Name'] == 0){
$filter_keys['Sheet Name'] = 'Worksheet';
}
// Encode back
$row['old_value'] = json_encode($old_data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
$row['new_value'] = json_encode($new_data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
$row['filter_keys'] = json_encode($filter_keys, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}
unset($row);
// GET COLUMN NAMES KEY AS LABEL_NAME VALUE AS VIEW NAME
$result = [];
$get_query = "SELECT label_name, view_name FROM cw_form_setting WHERE trans_status = 1";
$get_final_data = $this->db->query($get_query);
$get_final_result = $get_final_data->result();
$get_final_data->next_result();
foreach($get_final_result as $row){
$result[$row->label_name] = $row->view_name;
}
$hardcoded = ["loan_id" => "Loan Id","installment_count" => "Installment Count","install_year" => "Install Year","install_amount" => "Install Amount","employees_id" => "Employees Id","entry_status" => "Entry Status","check_status" => "Check Status","payroll_status" => "Payroll Status","prime_pre_audit_salary_id" => "Pre Audit Salary Id","trans_status" => "Trans Status","man_pay_type" => "Manual Pay Type","transactions_month" => "Transaction Month", "paid_days" => "Paid Days","supp_paid_days" => "Supplementary Paid Days","supp_month_days" => "Supplementary Month Days","lop_days" => "Lop Days","tot_year" => "Total Year","input_status" => "Input Status","penalty_total" => "Penalty Total","ptax_two_total" => "Ptax Two Total","penalty_balance" => "Penalty Balance","petty_cash_total" => "Petty Cash Total","ptax_two_balance" => "Ptax Two Balance","salary_loan_total" => "Salary Loan Total","petty_cash_balance" => "Petty Cash Balance","salary_loan_balance" => "Salary Loan Balance","penalty_instal_count" => "Penalty Instal Count","penalty_intallments" => "Penalty Intallments","ptax_two_installments" => "Ptax Two Installments","petty_cash_install_count" => "Petty Cash Install Count","petty_cash_installments" => "Petty Cash Installments","salary_loan_install_count" => "Salary Loan Install Count","sal_adv" => "Salary Advance","supplementary_status" => "Supplementary Status","prime_transactions_id" => "Prime Transactions Id","ptax_two_instal_count" => "Ptax Two Instal Count","ptax_two_installments" => "Ptax Two Installments","difference_value" => "Difference Value","prime_loan_installments_id" => "Prime Loan Installments Id","petty_cash_instal_count" =>"Petty Cash Instal Count", "salary_loan_instal_count" =>"Salary Loan Instal Count","salary_loan_installments" => "Salary Loan Installments", "penalty_installments" =>"Penalty Installments","prime_loan_installment_id"=>"Prime Loan Installment Id","paid_status"=>"Paid Status", "new_value" => "New Value","old_value" => "Old Value","arrear_date" => "Arrear Date","arrear_month" => "Arrear Month", "arrear_value" => "Arrear Value","arrear_column" => "Arrear Column","payroll_done" => "Payroll Done","prime_cumulative_id" => "Prime Cumulative Id", "increment_apply_month" => "Increment Apply Month", "increment_approval_id" => "Increment Approval Id","column_name" => "Column Name","prime_increment_id" => "Prime Increment Id","prime_arrears_id" => "Prime Arrears Id","transaction_column" => "Transaction Column","prime_monthly_input_id"=>"Prime Monthly Input Id","current_value"=> "Current Value","foreclose_sts" => "Foreclosure Status","loan_foreclose_date" => "Loan Foreclosure Date","loan_foreclose_amount"=> "Loan Foreclosure Amount" ,"fandf" => "Is Fandf","balance_days" => "Balance Days","prev_credit" => "Previous Credit Days","prime_lop_credit_id" =>"Prime Lop Credit Id"];
$result = array_merge($result, $hardcoded);
// REPLACE THE COlUMN NAMES
foreach($search_result as &$row){
// Decode old_value and new_value JSON
$old_data = json_decode($row['old_value'], true);
$new_data = json_decode($row['new_value'], true);
$original_old_data = $old_data; // keep original
$original_new_data = $new_data;
if(isset($row['module_name']) && isset($module_map[$row['module_name']])){
$row['module_name'] = $module_map[$row['module_name']];
}
// Replace keys using mapping
if(!empty($old_data)){
foreach($old_data as $id => $cols){
$updated_cols = [];
foreach($cols as $key => $val){
$updated_cols[$result[$key] ?? $key] = $val; // replace if mapping exists
}
$old_data[$id] = $updated_cols;
}
}
if(!empty($new_data)){
foreach($new_data as $id => $cols){
$updated_cols = [];
foreach($cols as $key => $val){
$updated_cols[$result[$key] ?? $key] = $val;
}
$new_data[$id] = $updated_cols;
}
}
$allEmptyOld = is_array($old_data) && count($old_data) > 0 && count(array_filter($old_data)) === 0;
$allEmptyNew = is_array($new_data) && count($new_data) > 0 && count(array_filter($new_data)) === 0;
if($allEmptyOld) $old_data = $original_old_data;
if($allEmptyNew) $new_data = $original_new_data;
// Save back as JSON
$row['old_value'] = json_encode($old_data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
$row['new_value'] = json_encode($new_data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}
unset($row);
echo json_encode(["draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,
"data"=> $search_result,]);
}
// EMPLOYEE SUGGESTION _ARN _16-10-2025
public function emp_suggest(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(['success' => false, 'message' => 'Url Expired.. Please refresh the page and try again..', 'table_data' => ""]);
exit;
}
$search_term = $this->input->post_get('term');
$final_qry = 'SELECT prime_employees_id, employee_code, emp_name FROM `cw_employees` WHERE termination_status = 0
AND(employee_code LIKE "'.$search_term.'%" OR emp_name LIKE "'.$search_term.'%")';
$final_data = $this->db->query("CALL sp_a_run('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
$suggestions = [];
foreach($final_result as $rslt){
$employee_id = $rslt->prime_employees_id;
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$suggestions[] = ['value' => $employee_id, 'label' => "$employee_code - $emp_name",'display_name' => "$employee_code - $emp_name",'employee_code' => $employee_code];
}
if(empty($suggestions)){
$suggestions[] = ['value' => "", 'label' => "No data found for this search"];
}
echo json_encode($suggestions);
}
// GET PICKLIST TO OUR DATAS TO MAPPINGS _ARN 16-10-2025
public function get_picklist_mappings(){
$picklist_qry = "SELECT label_name,MAX(pick_list) AS pick_list,MAX(pick_table) AS pick_table FROM cw_form_setting WHERE (field_type = 5 OR field_type = 7) AND trans_status = 1 AND pick_list_type = 1 AND label_name NOT IN ('employee_code') GROUP BY label_name";
$picklist_info = $this->db->query($picklist_qry);
$picklist_rslt = $picklist_info->result();
$picklist_info->next_result();
$picklist_data = [];
foreach($picklist_rslt as $row){
$label_name = strtolower(trim($row->label_name));
$pick_table = trim($row->pick_table);
$pick_list = trim($row->pick_list);
if(!$pick_table)continue;
// Check if table exists
$table_exists = $this->db->query("SHOW TABLES LIKE " . $this->db->escape($pick_table))->num_rows();
if(!$table_exists){
continue;
}
// Detect valid id/name/status columns
$id_col = null;
$name_col = null;
$status_col = null;
// If pick_list looks valid(no spaces, contains comma)
if($pick_list && strpos($pick_list, ',') !== false && !preg_match('/\s/', $pick_list)){
list($id_col, $name_col) = explode(',', $pick_list);
}
// Fallback: inspect table structure
if(!$id_col || !$name_col){
$desc = $this->db->query("SHOW COLUMNS FROM $pick_table")->result_array();
$cols = array_column($desc, 'Field');
foreach($cols as $col){
if(preg_match('/_id$/', $col)) $id_col = $col;
if(preg_match('/_value$/', $col) || preg_match('/name$/', $col)) $name_col = $col;
if(preg_match('/_status$/', $col) || $col === 'trans_status') $status_col = $col;
}
}
// Skip if still invalid
if(!$id_col || !$name_col) continue;
if(!$status_col) $status_col = 'trans_status';
// Safe select query
$qry = "SELECT $id_col AS id, $name_col AS name FROM $pick_table WHERE $status_col = 1";
try{
$rs = $this->db->query($qry)->result_array();
}catch(Exception $e){
continue;
}
// Build mapping
$mapping = [];
foreach($rs as $r){
$mapping[trim($r['id'])] = trim($r['name']);
}
$picklist_data[$label_name] = $mapping;
}
return $picklist_data;
}
// REPLACED THERE VALUE ON ORIGIN USER READABLITY _ARN 16-10-2025
public function replace_picklist_values($data, $picklist_data){
if(!is_array($data)) return $data;
array_walk_recursive($data, function(&$val, $key) use($picklist_data){
$key_lower = strtolower($key);
$common_ary = [
"input_status" => ["0" => "Pending","1" => "Processed","2" => "Modified"],"payroll_status" => ["0" => "Pending","1" => "Processed"],"process type" => ["1" => "Process By Employee","2" => "Process By Role","3" => "Process All",
"4" => "Process By Multi Employee"],"process mode" => ["1"=>"Process Payroll","2"=>"Delete Payroll","3"=>"View Payroll"],"fandf" => ["0"=>"Pending"] ,"esi_eligibility" =>["1" => "Yes", "2" => "No"],"trans_status" =>["1" => "Exists", "0" => "Deleted"],"foreclose_sts" =>["0"=>"Pending","1"=>"Closed"],"process_by" => ["1"=> "LOP Days","2"=> "Paid Days"]
];
if(isset($picklist_data[$key_lower])){
$mapping = $picklist_data[$key_lower];
// Handle comma-separated IDs
$ids = array_map('trim', explode(',', $val));
$replaced = [];
foreach($ids as $id){
if(isset($mapping[$id])){
$replaced[] = $mapping[$id];
}else{
// keep original if not found
$replaced[] = $id;
}
}
// If multiple names found, join with comma
$val = implode(', ', $replaced);
}
if(isset($common_ary[$key_lower])){
$mapping = $common_ary[$key_lower];
// Handle comma-separated numeric codes if any
$codes = array_map('trim', explode(',', $val));
$replaced = [];
foreach($codes as $code){
$replaced[] = $mapping[$code] ?? $code;
}
$val = implode(', ', $replaced);
}
});
return $data;
}
// GET REPORT AS EXCEL SHEET
public function export_all_to_excel(){
$encString = file_get_contents('php://input');
$post_data = $this->cryptoDecrypt($encString);
if(!is_array($post_data)){
echo json_encode(['success' => false,'message' => 'Invalid Request..']);
exit(0);
}
// INPUT PARAMS
$process_by = isset($post_data['process_by']) ? (int)$post_data['process_by'] : 0;
$module_id = isset($post_data['module_id']) ? $post_data['module_id'] : null;
$emp_id = isset($post_data['emp_id']) ? $post_data['emp_id'] : null;
$from_date = isset($post_data['from_date']) ? $post_data['from_date'] : null;
$to_date = isset($post_data['to_date']) ? $post_data['to_date'] : null;
$emp_code = isset($post_data['emp_code']) ? $post_data['emp_code'] : null;
// Optional Search value if you want same search functionality in export also
$search_value = isset($post_data['search_value']) ? trim($post_data['search_value']) : '';
if(!$module_id){
echo json_encode(['success' => false,'message' => 'Module is required for export.']);
exit(0);
}
// Module Map (replace module_name id to real name)
$modules_qry = "SELECT module_id,module_name FROM cw_modules WHERE show_module = 1 AND trans_status = 1 AND menu_id = 5 AND sub_menu_id = 3";
$modules_info = $this->db->query("CALL sp_a_run('SELECT',".$this->db->escape($modules_qry).")");
$modules_rslt = $modules_info->result();
$modules_info->next_result();
$module_map = [];
foreach($modules_rslt as $m){
$module_map[$m->module_id] = $m->module_name;
}
// WHERE CONDITION SAME AS search()
$where_qry = "";
$where_qry = " AND mal.module_name = ".$this->db->escape($module_id);
if($process_by === 1 && $emp_id){
$where_qry .= " AND mal.trans_created_by = ".$this->db->escape($emp_id);
}else
if($process_by === 2 && $from_date && $to_date){
$from_date = date('Y-m-d', strtotime($from_date));
$to_date = date('Y-m-d', strtotime($to_date));
$where_qry .= " AND mal.trans_created_date BETWEEN " . $this->db->escape($from_date . ' 00:00:00') .
" AND mal.trans_created_date AND " . $this->db->escape($to_date . ' 23:59:59');
}else
if($process_by === 3 && $emp_code){
$es_emp_code = $this->db->escape_str($emp_code);
$where_qry .= " AND FIND_IN_SET(".$this->db->escape($es_emp_code).", mal.employee_code)";
}
// MAIN QUERY
$main_query = "SELECT mal.prime_module_audit_log_id,mal.module_name,mal.filter_keys,mal.old_value,mal.new_value,
mal.action_type,CONCAT(cw_employees.employee_code,' - ',cw_employees.emp_name) AS created_by,mal.trans_created_date
FROM cw_module_audit_log AS mal JOIN cw_employees ON cw_employees.prime_employees_id = mal.trans_created_by
WHERE mal.trans_status = 1 AND mal.intract_type = 'user' $where_qry";
// SEARCH FILTER
if($search_value){
$search_value_esc = $this->db->escape_like_str($search_value);
$main_query .= " AND (JSON_SEARCH(mal.filter_keys, 'all', '%{$search_value_esc}%') IS NOT NULL OR JSON_SEARCH(mal.old_value, 'all', '%{$search_value_esc}%') IS NOT NULL OR JSON_SEARCH(mal.new_value, 'all', '%{$search_value_esc}%') IS NOT NULL OR mal.filter_keys LIKE '%\"{$search_value_esc}\"%' OR mal.old_value LIKE '%\"{$search_value_esc}\"%' OR mal.new_value LIKE '%\"{$search_value_esc}\"%' )";
}
$main_query .= " ORDER BY mal.trans_created_date DESC";
$audit_info = $this->db->query("CALL sp_a_run('SELECT',".$this->db->escape($main_query).")");
$audit_rslt = $audit_info->result();
$audit_info->next_result();
$search_result = json_decode(json_encode($audit_rslt), true);
if(empty($search_result)){
echo json_encode(['success' => false,'message' => "No data found for export."]);
exit(0);
}
// ===========================
// VALUE REPLACEMENT
// ===========================
// EXCEL FORMAT REPLACE
$excel_format_qry = "SELECT prime_excel_format_id, excel_name FROM cw_util_excel_format WHERE trans_status = 1";
$excel_format_rslt = $this->db->query($excel_format_qry)->result();
$excel_format_map = [];
foreach($excel_format_rslt as $ef){
$excel_format_map[$ef->prime_excel_format_id] = $ef->excel_name;
}
// PICKLIST
$picklist_data = $this->get_picklist_mappings();
// REPLACE PICKLIST VALUES + EXCEL FORMAT + SHEET NAME
foreach($search_result as &$row){
$old_data = json_decode($row['old_value'], true);
$new_data = json_decode($row['new_value'], true);
$filter_keys = json_decode($row['filter_keys'], true);
$old_data = $this->replace_picklist_values($old_data, $picklist_data);
$new_data = $this->replace_picklist_values($new_data, $picklist_data);
$filter_keys = $this->replace_picklist_values($filter_keys, $picklist_data);
if(isset($filter_keys['Excel Format'])){
$excel_id = $filter_keys['Excel Format'];
if(isset($excel_format_map[$excel_id])){
$filter_keys['Excel Format'] = $excel_format_map[$excel_id];
}
}
if(isset($filter_keys['Sheet Name']) && $filter_keys['Sheet Name'] == 0){
$filter_keys['Sheet Name'] = 'Worksheet';
}
$row['old_value'] = json_encode($old_data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
$row['new_value'] = json_encode($new_data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
$row['filter_keys'] = json_encode($filter_keys, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}
unset($row);
// COLUMN NAME REPLACE
$result = [];
$get_query = "SELECT label_name, view_name FROM cw_form_setting WHERE trans_status = 1";
$get_final_data = $this->db->query($get_query);
$get_final_result = $get_final_data->result();
$get_final_data->next_result();
foreach($get_final_result as $row){
$result[$row->label_name] = $row->view_name;
}
// HARDCODED MAP
$hardcoded = ["loan_id" => "Loan Id","installment_count" => "Installment Count","install_year" => "Install Year","install_amount" => "Install Amount","employees_id" => "Employees Id","entry_status" => "Entry Status","check_status" => "Check Status","payroll_status" => "Payroll Status","prime_pre_audit_salary_id" => "Pre Audit Salary Id","trans_status" => "Trans Status","man_pay_type" => "Manual Pay Type","transactions_month" => "Transaction Month", "paid_days" => "Paid Days","supp_paid_days" => "Supplementary Paid Days","supp_month_days" => "Supplementary Month Days","lop_days" => "Lop Days","tot_year" => "Total Year","input_status" => "Input Status","penalty_total" => "Penalty Total","ptax_two_total" => "Ptax Two Total","penalty_balance" => "Penalty Balance","petty_cash_total" => "Petty Cash Total","ptax_two_balance" => "Ptax Two Balance","salary_loan_total" => "Salary Loan Total","petty_cash_balance" => "Petty Cash Balance","salary_loan_balance" => "Salary Loan Balance","penalty_instal_count" => "Penalty Instal Count","penalty_intallments" => "Penalty Intallments","ptax_two_installments" => "Ptax Two Installments","petty_cash_install_count" => "Petty Cash Install Count","petty_cash_installments" => "Petty Cash Installments","salary_loan_install_count" => "Salary Loan Install Count","sal_adv" => "Salary Advance","supplementary_status" => "Supplementary Status","prime_transactions_id" => "Prime Transactions Id","ptax_two_instal_count" => "Ptax Two Instal Count","ptax_two_installments" => "Ptax Two Installments","difference_value" => "Difference Value","prime_loan_installments_id" => "Prime Loan Installments Id","petty_cash_instal_count" =>"Petty Cash Instal Count", "salary_loan_instal_count" =>"Salary Loan Instal Count","salary_loan_installments" => "Salary Loan Installments", "penalty_installments" =>"Penalty Installments","prime_loan_installment_id"=>"Prime Loan Installment Id","paid_status"=>"Paid Status", "new_value" => "New Value","old_value" => "Old Value","arrear_date" => "Arrear Date","arrear_month" => "Arrear Month", "arrear_value" => "Arrear Value","arrear_column" => "Arrear Column","payroll_done" => "Payroll Done","prime_cumulative_id" => "Prime Cumulative Id", "increment_apply_month" => "Increment Apply Month", "increment_approval_id" => "Increment Approval Id","column_name" => "Column Name","prime_increment_id" => "Prime Increment Id","prime_arrears_id" => "Prime Arrears Id","transaction_column" => "Transaction Column","prime_monthly_input_id"=>"Prime Monthly Input Id","current_value"=> "Current Value","foreclose_sts" => "Foreclosure Status","loan_foreclose_date" => "Loan Foreclosure Date","loan_foreclose_amount"=> "Loan Foreclosure Amount" ,"fandf" => "Is Fandf","balance_days" => "Balance Days","prev_credit" => "Previous Credit Days","prime_lop_credit_id" =>"Prime Lop Credit Id"];
$result = array_merge($result, $hardcoded);
// Replace keys for old_value/new_value + module name replace
foreach($search_result as &$row){
$old_data = json_decode($row['old_value'], true);
$new_data = json_decode($row['new_value'], true);
$original_old_data = $old_data;
$original_new_data = $new_data;
// module id -> module name
if(isset($row['module_name']) && isset($module_map[$row['module_name']])){
$row['module_name'] = $module_map[$row['module_name']];
}
if(!empty($old_data)){
foreach($old_data as $id => $cols){
$updated_cols = [];
foreach($cols as $key => $val){
$updated_cols[$result[$key] ?? $key] = $val;
}
$old_data[$id] = $updated_cols;
}
}
if(!empty($new_data)){
foreach($new_data as $id => $cols){
$updated_cols = [];
foreach($cols as $key => $val){
$updated_cols[$result[$key] ?? $key] = $val;
}
$new_data[$id] = $updated_cols;
}
}
$allEmptyOld = is_array($old_data) && count($old_data) > 0 && count(array_filter($old_data)) === 0;
$allEmptyNew = is_array($new_data) && count($new_data) > 0 && count(array_filter($new_data)) === 0;
if($allEmptyOld) $old_data = $original_old_data;
if($allEmptyNew) $new_data = $original_new_data;
$row['old_value'] = json_encode($old_data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
$row['new_value'] = json_encode($new_data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}
unset($row);
// ===========================
// EXCEL EXPORT
// ===========================
$this->load->library('xlsxwriter');
$writer = new XLSXWriter();
// HEADER
$header = ['Module' => 'string','Filter Keys' => 'string','Old Value' => 'string','New Value' => 'string','Action Type' => 'string','Changed By' => 'string','Date' => 'DD/MM/YYYY HH:MM:SS'];
$writer->writeSheetHeader('AuditLog', $header);
foreach ($search_result as $row){
$created_date = !empty($row['trans_created_date'])
? date('Y-m-d H:i:s', strtotime($row['trans_created_date']))
: '';
$writer->writeSheetRow('AuditLog', [
$this->excel_safe($row['module_name'] ?? ''),
$this->excel_safe($this->json_to_excel_text($row['filter_keys'] ?? '')),
$this->excel_safe($this->json_to_excel_text($row['old_value'] ?? '')),
$this->excel_safe($this->json_to_excel_text($row['new_value'] ?? '')),
$this->excel_safe($row['action_type'] ?? ''),
$this->excel_safe($row['created_by'] ?? ''),
$created_date
]);
}
// FILE NAME
$filename = "module_audit_report_" . $this->logged_id . "_" . date('Ymd_His') . ".xlsx";
// DOWNLOAD HEADERS
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="'.$filename.'"');
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Pragma: no-cache');
header('Expires: 0');
// OUTPUT
$writer->writeToStdOut();
exit;
}
// =============== JSON ALIGNMENT STRUCTURE ARRANGE =================
public function json_to_excel_text($json_string){
if(empty($json_string)) return '';
// if already array/object JSON
$obj = json_decode($json_string, true);
if(json_last_error() !== JSON_ERROR_NONE){
return trim(strip_tags((string)$json_string));
}
$lines = [];
$this->flatten_json_lines($obj, $lines);
return implode("\n", $lines);
}
// ========================= KEY & VALUES STRUCTURE ALIGN ================
public function flatten_json_lines($data, &$lines, $parent_key = ''){
if(!is_array($data)) return;
foreach($data as $key => $val){
// numeric keys also possible
$key_name = is_string($key) ? trim($key) : $key;
if(is_array($val)){
// nested structure
if($parent_key !== ''){
$lines[] = $parent_key . " - " . $key_name . ":";
$this->flatten_json_lines($val, $lines, '');
}else{
// Keep key header if it has nested values
$lines[] = $key_name . ":";
$this->flatten_json_lines($val, $lines, '');
}
}else{
$val_text = ($val === null) ? '' : trim((string)$val);
if($key_name !== '' || $val_text !== ''){
if($parent_key !== ''){
$lines[] = $parent_key . " - " . $key_name . ": " . $val_text;
}else{
$lines[] = $key_name . ": " . $val_text;
}
}
}
}
}
// ========== CHARCTERS CLEAN ============
public function excel_safe($value){
if($value === null) return '';
$value = (string)$value;
// Remove invalid XML characters (Excel killer)
$value = preg_replace('/[^\x09\x0A\x0D\x20-\x7E\xA0-\x{10FFFF}]/u', '', $value);
// Force valid UTF-8
$value = mb_convert_encoding($value, 'UTF-8', 'UTF-8');
// Excel cell limit (32,767 chars)
if(strlen($value) > 32767){
$value = substr($value, 0, 32767);
}
return $value;
}
}