File: //home/cafsindia/hrms_patroniss_com/application/controllers/Audit_log.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Audit_log extends Action_controller{
public function __construct(){
parent::__construct('audit_log');
$this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$form_view_qry = 'select prime_form_view_id, form_view_heading from cw_form_view_setting where prime_view_module_id = "employees" and form_view_type = 3 and form_view_show = 1 and trans_status = 1 order by form_view_sort';
$form_view_data = $this->db->query("CALL sp_a_run ('SELECT','$form_view_qry')");
$form_view_result = $form_view_data->result();
$form_view_data->next_result();
$tab_list = array();
foreach($form_view_result as $form_view){
$prime_form_view_id = $form_view->prime_form_view_id;
$form_head = $form_view->form_view_heading;
$tab_list[$prime_form_view_id] = $form_head;
}
$data['tab_list'] = $tab_list;
$user_data_qry = 'select user_name from cw_employees where trans_status = 1 and user_right in (1,2) order by prime_employees_id';
$user_data = $this->db->query("CALL sp_a_run ('SELECT','$user_data_qry')");
$user_result = $user_data->result();
$user_data->next_result();
$user_list = array(""=>"---- Select User ----");
foreach($user_result as $user){
$user_name = $user->user_name;
$user_id = $user->user_name;
$user_list[$user_id] = $user_name;
}
$this->fliter_list[] = array('label_id'=> 'user_name', 'label_name'=> "User Name", 'field_isdefault'=> 1, 'array_list'=> $user_list, 'field_type'=> 5);
$emp_data_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 order by prime_employees_id';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_data_qry')");
$emp_result = $emp_data->result();
$emp_data->next_result();
$emp_list = array();
foreach($emp_result as $emp_rslt){
$employee_code = $emp_rslt->employee_code;
$emp_name = $emp_rslt->emp_name;
$emp_list[$employee_code] = $emp_name;
}
$this->fliter_list[] = array('label_id'=> 'emp_name', 'label_name'=> "Employee Name", 'field_isdefault'=> 1, 'array_list'=> $emp_list, 'field_type'=> 5);
$data['fliter_list'] = $this->fliter_list;
$this->load->view("$this->control_name/manage",$data);
}
public function check_audit_log(){
$start_date = $this->input->post('start_date');
$end_date = $this->input->post('end_date');
$view_type = $this->input->post('view_type');
$tab_list = ltrim(implode(",",$this->input->post('tab_list[]')),",");
$user_name = ltrim(implode(",",$this->input->post('user_name[]')),",");
$fil_emp_name = ltrim(implode(",",$this->input->post('emp_name[]')),",");
if($user_name){
$user_name = '"'.str_replace(",",'","',$user_name).'"';
$user_qry = " and user_name in ($user_name)";
}else{
$user_qry = "";
}
if($fil_emp_name){
$fil_emp_name = '"'.str_replace(",",'","',$fil_emp_name).'"';
if((int)$view_type === 2){
$emp_qry = " and row_set_emp.employee_code in ($fil_emp_name)";
}else{
$emp_qry = " and cw_employees_log.employee_code in ($fil_emp_name)";
}
}else{
$emp_qry = "";
}
if((int)$view_type === 2){
$emp_rowsetlog_qry = 'select *,row_set_emp.employee_code,row_set_emp.emp_name from cw_row_set_log inner join cw_form_setting on cw_form_setting.label_name=cw_row_set_log.label_name inner join cw_form_view_setting on cw_form_view_setting.prime_form_view_id=cw_row_set_log.row_set_view_id inner join cw_employees as row_set_emp on row_set_emp.prime_employees_id=cw_row_set_log.prime_employee_id inner join cw_employees on cw_employees.prime_employees_id=cw_row_set_log.ceated_by where cw_form_setting.prime_module_id = "employees" and row_set_view_id in ('.$tab_list.') and date_format(cw_row_set_log.created_date, "%Y-%m-%d") >= "'.$start_date.'" and date_format(cw_row_set_log.created_date, "%Y-%m-%d") <= "'.$end_date.'" '.$user_qry.' '.$emp_qry.' group by prime_row_set_log';
$emp_rowsetlog_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_rowsetlog_qry')");
$emp_rowsetlog_rslt = $emp_rowsetlog_data->result();
$emp_rowsetlog_data->next_result();
if($emp_rowsetlog_rslt){
$tr_line = "";
$i = 1;
foreach($emp_rowsetlog_rslt as $emp_rowlog){
$employee_code = $emp_rowlog->employee_code;
$emp_name = $emp_rowlog->emp_name;
$label_name = $emp_rowlog->label_name;
$view_name = $emp_rowlog->view_name;
$field_type = $emp_rowlog->field_type;
$old_value = $emp_rowlog->old_value;
$new_value = $emp_rowlog->new_value;
$pick_list_type = (int)$emp_rowlog->pick_list_type;
$form_heading = ucwords($emp_rowlog->form_view_heading);
$created_date = date('d-m-Y H:i:s',strtotime($emp_rowlog->created_date));
$user_name = $emp_rowlog->user_name;
$form_heading = ucwords($emp_rowlog->form_view_heading);
if((int)$field_type === 4){
$old_value = date('d-m-Y',strtotime($old_value));
$new_value = date('d-m-Y',strtotime($new_value));
}else
if(((int)$field_type === 5) || ((int)$field_type === 7)){
$pick_table = $emp_rowlog->pick_table;
$pick_list = $emp_rowlog->pick_list;
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_list_val_3 = "trans_status";
}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";
}
if($old_value){
$pick_query = 'select '.$pick_list_val_2.' as old_val from '.$pick_table.' where '.$pick_list_val_1.' = "'.$old_value.'" and '.$pick_list_val_3.' = 1';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_rslt = $pick_data->result();
$pick_data->next_result();
$old_value = $pick_rslt[0]->old_val;
}
if($new_value){
$pick_query = 'select '.$pick_list_val_2.' as new_val from '.$pick_table.' where '.$pick_list_val_1.' = "'.$new_value.'" and '.$pick_list_val_3.' = 1';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_rslt = $pick_data->result();
$pick_data->next_result();
$new_value = $pick_rslt[0]->new_val;
}
}
$tr_line .= "<tr><td>$i</td><td>$employee_code</td><td>$emp_name</td><td>$form_heading</td><td>$view_name</td><td style='width:20%;'>$old_value</td><td style='width:20%;'>$new_value</td><td>$user_name</td><td>$created_date</td></tr>";
$i++;
}
$thead_line ="<tr><th>Si.No</th><th>Employee Code</th><th>Employee Name</th><th>Form Name</th><th>Label Name</th><th>Old Value</th><th>New Value</th><th>User Name</th><th>Created Date</th></tr>";
$table_content = "<table id='table' class='table table-striped table-hover' style='width:100% !important;'>
<thead>
$thead_line
</thead>
<tbody>
$tr_line
</tbody>
</table>";
echo json_encode(array('success' => TRUE, 'message' => "See master change details",'table_content'=>$table_content));
}else{
echo json_encode(array('success' => FALSE, 'message' => "No data is available"));
}
}else{
$emp_log_qry = 'select *,cw_employees_log.trans_created_date,cw_employees_log.employee_code,cw_employees_log.emp_name,user_name from cw_employees_log inner join cw_form_setting on cw_form_setting.label_name=cw_employees_log.label_name inner join cw_employees on cw_employees.prime_employees_id=cw_employees_log.trans_created_by where prime_module_id = "employees" and date_format(cw_employees_log.trans_created_date, "%Y-%m-%d") >= "'.$start_date.'" and date_format(cw_employees_log.trans_created_date, "%Y-%m-%d") <= "'.$end_date.'" '.$user_qry.' '.$emp_qry.' group by prime_employees_log_id order by cw_employees_log.trans_created_date';
$emp_log_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_log_qry')");
$emp_log_rslt = $emp_log_data->result();
$emp_log_data->next_result();
if($emp_log_rslt){
$tr_line = "";
$i = 1;
foreach($emp_log_rslt as $emp_log){
$employee_code = $emp_log->employee_code;
$emp_name = $emp_log->emp_name;
$label_name = $emp_log->label_name;
$view_name = $emp_log->view_name;
$field_type = $emp_log->field_type;
$old_value = $emp_log->old_value;
$new_value = $emp_log->new_value;
$pick_list_type = (int)$emp_log->pick_list_type;
$create_date = date('d-m-Y H:i:s',strtotime($emp_log->trans_created_date));
$user_name = $emp_log->user_name;
if((int)$field_type === 4){
$old_value = date('d-m-Y',strtotime($old_value));
$new_value = date('d-m-Y',strtotime($new_value));
}else
if(((int)$field_type === 5) || ((int)$field_type === 7)){
$pick_table = $emp_log->pick_table;
$pick_list = $emp_log->pick_list;
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_list_val_3 = "trans_status";
}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";
}
if($old_value){
$pick_query = 'select '.$pick_list_val_2.' as old_val from '.$pick_table.' where '.$pick_list_val_1.' = "'.$old_value.'" and '.$pick_list_val_3.' = 1';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_rslt = $pick_data->result();
$pick_data->next_result();
$old_value = $pick_rslt[0]->old_val;
}
if($new_value){
$pick_query = 'select '.$pick_list_val_2.' as new_val from '.$pick_table.' where '.$pick_list_val_1.' = "'.$new_value.'" and '.$pick_list_val_3.' = 1';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_rslt = $pick_data->result();
$pick_data->next_result();
$new_value = $pick_rslt[0]->new_val;
}
}
$tr_line .= "<tr><td>$i</td><td>$employee_code</td><td>$emp_name</td><td>$view_name</td><td style='width:20%'>$old_value</td><td style='width:20%'>$new_value</td><td>$user_name</td><td>$create_date</td></tr>";
$i++;
}
$thead_line ="<tr><th>Si.No</th><th>Employee Code</th><th>Employee Name</th><th>Label Name</th><th>Old Value</th><th>New Value</th><th>User Name</th><th>Created Date</th></tr>";
$table_content = "<table id='table' class='table table-striped table-hover' style='width:100% !important;'>
<thead>
$thead_line
</thead>
<tbody>
$tr_line
</tbody>
</table>";
echo json_encode(array('success' => TRUE, 'message' => "See master change details",'table_content'=>$table_content));
}else{
echo json_encode(array('success' => FALSE, 'message' => "No data is available"));
}
}
}
}
?>