File: /home/cafsindia/hrms_cafsinfotech_in/application/controllers/Service_record.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Service_record extends Action_controller{
public function __construct(){
parent::__construct('service_record');
$this->load->model('Process_payroll_model');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$get_earning_qry = 'SELECT prime_form_id,prime_module_id,label_name,view_name FROM `cw_form_setting` WHERE prime_module_id = "employees" and field_type in (5,7,9) ';
$category_info = $this->db->query("CALL sp_a_run ('SELECT','$get_earning_qry')");
$category_result = $category_info->result();
$category_info->next_result();
$category_list[""] = "---- Category For ----";
$category_list["increment"] = "Increment";
foreach($category_result as $for){
$cat_id = $for->label_name;
$view_name = $for->view_name;
$category_list[$cat_id] = $view_name;
}
$data['category_list'] = $category_list;
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//AUTO COMPLETE FUNCTION
public function emp_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_get('term');
$final_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 and prime_employees_id !=1 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();
foreach($final_result as $rslt){
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$suggestions[] = array('value' => "$employee_code", 'label' => "$employee_code - $emp_name");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
//DISPLAY TABLE
public function check_service_record(){
$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);
}
$employee_code = $this->input->post('employees');
$category = implode('","',$this->input->post('category'));
$this->view_info('-1');
$master_pick = $this->pick_list; //all pick result
//FOR INCREMENT
if($employee_code && $category === 'increment'){
$increment_qry = 'SELECT DATE_FORMAT(cw_increment.trans_created_date, "%d-%m-%Y") AS `Created Date`,current_value AS `From`,new_value AS `To`, view_name AS `Component` FROM cw_increment INNER JOIN cw_form_setting ON cw_form_setting.label_name = cw_increment.column_name WHERE cw_increment.employee_code = "'.$employee_code.'" AND cw_increment.trans_status = 1 order by cw_increment.trans_created_date';
$increment_info = $this->db->query("CALL sp_a_run('SELECT', '$increment_qry')");
$increment_result = $increment_info->result_array();
$increment_info->next_result();
if($increment_result){
$table = $this->generateTable($increment_result);
echo json_encode(array("success" => TRUE,'increment'=>$table));
}else{
echo json_encode(array('success'=>FALSE,'message'=>'No Records Found.!'));
}
//FOR EMPLOYEE LOG
}else
if($employee_code){
$drop_qry = 'DROP TABLE IF EXISTS tbl_service_record';
$drop_info = $this->db->query("CALL sp_a_run ('RUN','$drop_qry')");
$log_qry = 'CREATE TABLE tbl_service_record SELECT trans_created_date,trans_created_by ,employee_code,old_value ,new_value ,label_name FROM cw_employees_log where employee_code = "'.$employee_code.'" and label_name in ("'.$category.'") AND trans_status = 1 ';
$log_info = $this->db->query("CALL sp_a_run ('RUN','$log_qry')");
$inc_qry = 'INSERT INTO tbl_service_record (trans_created_date,trans_created_by,employee_code, old_value, new_value, label_name) SELECT trans_created_date,trans_created_by,employee_code, current_value, new_value, column_name FROM cw_increment where employee_code = "'.$employee_code.'" AND trans_status = 1';
$inc_info = $this->db->query("CALL sp_a_run('RUN', '$inc_qry')");
// Get Result from temp table
$emp_log_qry = 'SELECT tbl_service_record.trans_created_date,CONCAT(cw_employees.employee_code,"~", cw_employees.emp_name) AS emp_name,tbl_service_record.old_value,tbl_service_record.new_value,tbl_service_record.label_name FROM tbl_service_record INNER JOIN cw_form_setting ON cw_form_setting.label_name = tbl_service_record.label_name join cw_employees on cw_employees.prime_employees_id = tbl_service_record.trans_created_by WHERE tbl_service_record.employee_code = "'.$employee_code.'" and tbl_service_record.label_name in ("'.$category.'") order by tbl_service_record.trans_created_date';
$emp_log_info = $this->db->query("CALL sp_a_run('SELECT', '$emp_log_qry')");
$emp_log_result = $emp_log_info->result();
$emp_log_info->next_result();
//form setting for view name details
$form_view_qry = 'select label_name,view_name,field_type from cw_form_setting where prime_module_id="employees" and trans_status=1';
$form_view_data = $this->db->query("CALL sp_a_run ('SELECT','$form_view_qry')");
$form_view_result = $form_view_data->result_array();
$form_view_data->next_result();
$form_view_rslt = array_reduce($form_view_result, function ($result, $arr){
$result[$arr['label_name']] = $arr;
return $result;
}, array());
$log_result = [];
foreach($emp_log_result as $emp_log_rslt){
$label_name = $emp_log_rslt->label_name;
$emp_name = $emp_log_rslt->emp_name; //EMP NAME
$old_value = $emp_log_rslt->old_value; //FROM
$new_value = $emp_log_rslt->new_value; //TO
$change_date = date('d-m-Y',strtotime($emp_log_rslt->trans_created_date)); //CREATED DATE
$view_name = $form_view_rslt[$label_name]['view_name']; //COMPONENT
$field_type = (int)$form_view_rslt[$label_name]['field_type'];
if($field_type === 4){
if($old_value){
$old_value = date('d-m-Y',strtotime($old_value));
}
if($new_value){
$new_value = date('d-m-Y',strtotime($new_value));
}
}else
if($field_type === 13){
if($old_value){
$old_value = date('d-m-Y H:i:s',strtotime($old_value));
}
if($new_value){
$new_value = date('d-m-Y H:i:s',strtotime($new_value));
}
}else
if($field_type === 5 || $field_type === 7 || $field_type === 9){
if($old_value){
$old_value = $master_pick[$label_name]['array_list'][$old_value];
}if($new_value){
$new_value = $master_pick[$label_name]['array_list'][$new_value];
}
}
$data = ["Created Date" => $change_date,"Created By" => $emp_name,"From" => $old_value,"To" => $new_value,"Component" => $view_name];
$log_result[] = $data;
}
// echo "<pre>";
// print_r($data);
// die;
if($log_result){
$table = $this->generateTable($log_result);
echo json_encode(array("success" => TRUE,'increment'=>$table));
}else{
echo json_encode(array('success'=>FALSE,'message'=>'No Records Found.!'));
}
}
}
//Generating table !
public function generateTable($data){
// echo "<pre>";
// print_r($data);
// die;
if($data){
$groupheaders = [];
foreach($data as $value){
$header = $value['Component'];
$groupheaders[$header][] = $value;
}
$result = '<table class="display" id="role">';
$keys = array_keys((array) $data[0]);
$result .= '<thead><tr>';
foreach ($keys as $key){
$result .= '<th>'.htmlspecialchars($key).'</th>';
}
$result .= '</tr></thead>';
foreach($groupheaders as $header => $group){
$result .= '<tr><th colspan="'.count($keys).'" id="category-header">'.htmlspecialchars($header).'</th></tr>';
foreach ($group as $value){
$result .= '<tr>';
foreach ($value as $tbody){
$result .= '<td>'.htmlspecialchars($tbody).'</td>';
}
$result .= '</tr>';
}
}
$result .= '</table>';
return $result;
}else{
echo json_encode(array('success'=>FALSE,'message'=>'No Records Found.!'));
}
}
}
?>