File: /home/cafsindia/uds.cafsinfotech.in/application/controllers/Pre_audit_misc_salary.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Pre_audit_misc_salary extends Action_controller{
public function __construct(){
parent::__construct('pre_audit_misc_salary');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$this->misc_payment_info();
$data['table_head'] = $this->table_head;
$data['all_pick'] = $this->pick_list;
$add_heads = array('employer_esi'=>"Employer Esi",'employee_esi'=>"Employee Esi",'employer_pf'=>"Employer PF",'employee_pf'=>"Employee PF",'total_earnings'=>"Total Earnings",'total_deductions'=>"Total Deductions",'net_pay'=>"Net Pay");
$data['add_heads'] = $add_heads;
//Form Data - START BSK
$logged_area_access = rtrim($this->logged_area_access,',');
$per_area_qry = 'SELECT personal_code,personal_name FROM `cw_sap_personal_area` where trans_status = 1 and cw_sap_personal_area.personal_code in ('.$logged_area_access.')';
$per_area_info = $this->db->query("CALL sp_a_run ('SELECT','$per_area_qry')");
$per_area_rslt = $per_area_info->result();
$per_area_info->next_result();
$per_area_list[""] = "---- Select Personal Area ----";
foreach ($per_area_rslt as $for) {
$personal_code = $for->personal_code;
$personal_name = $for->personal_name;
$per_area_list[$personal_code] = $personal_code.' - '.$personal_name;
}
$data['per_area_list'] = $per_area_list;
$this->logged_area_access = $this->session->userdata('logged_area_access');
$logged_area_access = rtrim($this->logged_area_access,',');
$project_qry = 'SELECT pro_id,pro_desc FROM `cw_sap_project` where trans_status = 1 and pro_personal_area_id in ('.$logged_area_access.')';
$project_info = $this->db->query("CALL sp_a_run ('SELECT','$project_qry')");
$project_rslt = $project_info->result();
$project_info->next_result();
$project_list[""] = "---- Select Project Id ----";
foreach ($project_rslt as $for) {
$pro_id = $for->pro_id;
$pro_desc = $for->pro_desc;
$project_list[$pro_id] = $pro_id.' - '.$pro_desc;
}
$data['project_list'] = $project_list;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//View Transaction Data
public function view_report(){
$personal_code = $this->input->post('personal_code');
$search_month = $this->input->post('search_month');
$project = $this->input->post('project');
$save_info = $this->db->query("CALL itsp_misc_payment('$personal_code','$project','$search_month')");
$save_result = $save_info->result();
$save_info->next_result();
if($save_result){
echo json_encode(array("status" => TRUE,"message" => "Processed Successfully"));
}else{
echo json_encode(array("status" => FALSE,"message" => "Try After Sometime..."));
}
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
public function search(){
//SEARCH INFO FUNCTION
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$order = $this->input->post('order');
$order_col = $this->input->post('columns');
$search = $this->input->post('search');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$order_col = $order_col[$column]['data'];
$search = trim($search['value']);
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$access_data = $this->session->userdata('access_data');
$search_month = $this->input->post('search_month');
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$filter_emp_code = $this->input->post('filter_emp_code');
$search_mon_date = date("Y-m-d",strtotime("01-".$search_month));
$add_query = "";
$emp_qry = "";
$add_column = "";
$label_name_arr = array();
$this->prime_table = "cw_misc_input_fms";
$start_date = date("Y-m-d",strtotime('01-'.$search_month));
$end_date = date("Y-m-t",strtotime($start_date));
//MISC SELECT LABEL
$un_select_cols = '"employee_esi","employer_esi","employee_pf","employer_pf","eps","total_earnings","total_deductions","net_pay"';
// and field_show = "1"
$mi_inp_sel_qry = 'select label_name from cw_form_setting where prime_module_id = "misc_input_fms" and table_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and label_name not in ('.$un_select_cols.') ORDER BY input_for,field_sort asc';
$mi_inp_sel_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_inp_sel_qry')");
$mi_inp_sel_rslt = $mi_inp_sel_data->result();
$mi_inp_sel_data->next_result();
$mi_inp_arr = array_reduce($mi_inp_sel_rslt ?? [], function($result, $arr){
$result[$arr->label_name] = $arr->label_name;
return $result;
}, array());
$mi_ins_list = implode(',',$mi_inp_arr ?? []);
$mi_sel_list = 'cw_misc_input_fms.'.implode(',cw_misc_input_fms.',$mi_inp_arr ?? []);
//include static components
$mi_sel_list .= ',cw_misc_input_fms.employer_esi,cw_misc_input_fms.employee_esi,cw_misc_input_fms.employer_pf,cw_misc_input_fms.employee_pf,cw_misc_input_fms.total_earnings,cw_misc_input_fms.total_deductions,cw_misc_input_fms.net_pay';
$emp_inp_sel_qry = 'select label_name from cw_form_setting where prime_module_id in ("employees") and field_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and (earn_month_check = "1" OR deduction_month_check = "1") and label_name in ("employee_code","emp_name","personal_code","project_id","wbs_element","position","role","activity_no","termination_status","date_of_joining","date_of_birth") ORDER BY input_for,field_sort asc';
$emp_inp_sel_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_inp_sel_qry')");
$emp_inp_sel_rslt = $emp_inp_sel_data->result();
$emp_inp_sel_data->next_result();
$emp_inp_arr = array_reduce($emp_inp_sel_rslt ?? [], function($result, $arr){
$result[$arr->label_name] = $arr->label_name;
return $result;
}, array());
$emp_ins_list = implode(',',$emp_inp_arr ?? []);
$emp_sel_list = 'cw_misc_input_fms.'.implode(',cw_misc_input_fms.',$emp_inp_arr ?? []).',cw_misc_input_fms.payroll,cw_misc_input_fms.entry_status,cw_misc_input_fms.check_status,cw_misc_input_fms.input_status';
$common_search = "";
if($search){
$common_search .= ' and (cw_misc_input_fms.personal_code like "'.$search.'%" or cw_misc_input_fms.project_id like "'.$search.'%" or cw_misc_input_fms.wbs_element like "'.$search.'%" or cw_misc_input_fms.activity_no like "'.$search.'%" or cw_misc_input_fms.employee_code like "'.$search.'%")';
}
//get all count
$count_query = "select count(*) as allcount from $this->prime_table where $this->prime_table.trans_status = 1 and $this->prime_table.process_month =\"".$search_month."\" ".$common_search." and $this->prime_table.personal_code = \"".$personal_code."\" and $this->prime_table.project_id = \"".$project."\" order by $this->prime_table.$order_col $order_sor";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
// ,cw_misc_input_fms.with_esi,cw_misc_input_fms.with_pf
$search_query = "SELECT cw_misc_input_fms.prime_misc_input_fms_id,$emp_sel_list,$mi_sel_list FROM $this->prime_table inner join cw_employees on cw_employees.employee_code = $this->prime_table.employee_code where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" ".$common_search." and $this->prime_table.personal_code = \"".$personal_code."\" and check_status = 0 and $this->prime_table.project_id = \"".$project."\" order by $this->prime_table.$order_col $order_sor";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $num_rows,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
public function view_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$created_on = date('Y-m-d H:i:s');
$search_month = $this->input->post('process_month');
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$this->misc_payment_info();
$table_head_rslt = $this->table_head;
$pick_list_arr = $this->pick_list;
$export_pick = $this->export_pick_arr;
$hide_col_arr = array("personal_code"=>"Personal Code","termination_status"=>"Employee Status","project_id"=>"Project Id","role"=>"Category");
// $table_head_arr = array_reduce($table_head_rslt, function($result, $arr){
// $result[$arr['label_name']] = $arr['view_name'];
// return $result;
// }, array());
$table_head_arr = array();
$ear_ded_col_arr = array();
foreach($table_head_rslt as $arr){
$table_head_arr[$arr->label_name] = $arr->view_name;
$transaction_type = (int)$arr->transaction_type;
//EARNINGS AND DEDUCTIONS COLUMNS ONLY PUSH
if($transaction_type === 2 || $transaction_type === 3){
$ear_ded_col_arr[$arr->label_name] = $arr->transaction_type;
}
}
$table_head_arr = array_diff_key($table_head_arr ?? [], $hide_col_arr ?? []);
//Create Query
//this default columns only show for calculation entry table not to all so we should not take in query
$un_select_cols = '"employee_esi","employer_esi","employee_pf","employer_pf","eps","total_earnings","total_deductions","net_pay"';
// and field_show = "1"
$mi_inp_sel_qry = 'select label_name from cw_form_setting where prime_module_id = "misc_input_fms" and table_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and label_name not in ('.$un_select_cols.') ORDER BY input_for,table_sort asc';
$mi_inp_sel_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_inp_sel_qry')");
$mi_inp_sel_rslt = $mi_inp_sel_data->result();
$mi_inp_sel_data->next_result();
$mi_inp_arr = array_reduce($mi_inp_sel_rslt ?? [], function($result, $arr){
$result[$arr->label_name] = $arr->label_name;
return $result;
}, array());
$mi_sel_list = 'misc.'.implode(',misc.',$mi_inp_arr ?? []);
$emp_inp_sel_qry = 'select label_name from cw_form_setting where prime_module_id in ("employees") and field_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and label_name in ("role","employee_code","emp_name","personal_code","project_id","wbs_element","position","activity_no","termination_status") ORDER BY monthly_input_sort asc';
$emp_inp_sel_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_inp_sel_qry')");
$emp_inp_sel_rslt = $emp_inp_sel_data->result();
$emp_inp_sel_data->next_result();
$column_arr = array_reduce($emp_inp_sel_rslt ?? [], function($result, $arr){
$result[$arr->label_name] = $arr->label_name;
return $result;
}, array());
$emp_sel_list = 'misc.'.implode(',misc.',$column_arr ?? []).',misc.payroll,misc.entry_status,misc.check_status,misc.input_status';
// ,misc.with_esi,misc.with_pf,misc.remarks //and cw_pay_structure.activity_no = misc.activity_no
$search_query = "SELECT misc.prime_misc_input_fms_id,$emp_sel_list,$mi_sel_list".",misc.employee_esi,misc.employer_esi,misc.employee_pf,misc.employer_pf,misc.eps,misc.total_earnings,misc.total_deductions,misc.net_pay,cw_pay_structure.gross FROM cw_misc_input_fms misc inner join cw_employees on cw_employees.employee_code = misc.employee_code inner join cw_pay_structure on (cw_pay_structure.personal_code = misc.personal_code and cw_pay_structure.wbs_element = misc.wbs_element and cw_pay_structure.position = misc.position and date_format(cw_pay_structure.from_date, '%Y-%m') <= date_format(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m') and date_format(cw_pay_structure.to_date, '%Y-%m') >= date_format(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m')) where misc.trans_status = 1 and cw_pay_structure.trans_status = 1 and misc.process_month=\"".$search_month."\" and misc.personal_code = \"".$personal_code."\" and misc.project_id = \"".$project."\" and misc.entry_status = 1 and misc.check_status = 0 order by misc.prime_misc_input_fms_id ASC";
$search_data = $this->db->query($search_query);
$search_result = $search_data->result_array();
$search_data->next_result();
if(!count($search_result[0] ?? [])){
echo json_encode(array("success" => FALSE,"message" => "No Data Available..!","table_data" => '','export_pick' => ''));
}else{
//TABLE CREATION START
//without decimal inputs get from misc input fms module
// and field_show = "1
$mi_pick_inp_qry = 'select label_name,field_type from cw_form_setting where prime_module_id = "misc_input_fms" and table_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and field_type <> 2 ORDER BY input_for,table_sort asc';
$mi_pick_inp_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_pick_inp_qry')");
$mi_pick_inp_rslt = $mi_pick_inp_data->result_array();
$mi_pick_inp_data->next_result();
$mi_pick_inp_arr = array();
foreach($mi_pick_inp_rslt as $pick_inp_key => $pick_inp_value){
$mi_field_type = $pick_inp_value['field_type'];
$mi_inp_name = $pick_inp_value['label_name'];
$mi_pick_inp_arr[$mi_inp_name] = $mi_inp_name;
}
//for table header and footer arrays
$mi_cal_inp_arr = array();
$footer_sum_arr = array();
foreach($search_result as $key => $value){
foreach($mi_inp_arr as $mi_key => $mi_label){
//condition for only decimal fields get
if(!in_array($mi_label,$mi_pick_inp_arr)){
$mi_val = number_format((float)$value[$mi_label], 2, '.', '');
if($mi_val > 0.00 && !$column_arr[$mi_label]){
$mi_cal_inp_arr[$mi_label] = $mi_label;
}
//for footer sum array
$footer_sum_arr[$mi_label] += $mi_val;
}
}
$footer_sum_arr['employee_esi'] += $value['employee_esi'];
$footer_sum_arr['employer_esi'] += $value['employer_esi'];
$footer_sum_arr['employee_pf'] += $value['employee_pf'];
$footer_sum_arr['employer_pf'] += $value['employer_pf'];
$footer_sum_arr['eps'] += $value['eps'];
$footer_sum_arr['total_earnings'] += $value['total_earnings'];
$footer_sum_arr['total_deductions'] += $value['total_deductions'];
$footer_sum_arr['net_pay'] += $value['net_pay'];
}
// GET TABLE ORDER BASED DECIMAL ARRAY (NOT 0 VALUE DECIMAL COLUMNS(only get calculated inputs))
$mi_cal_inp_arr = array_intersect($mi_inp_arr ?? [],$mi_cal_inp_arr ?? []);
//0 VALUE COLUMN GET ARRAY
$mi_remove_arr = array_diff($mi_inp_arr ?? [],$mi_cal_inp_arr ?? [],$mi_pick_inp_arr ?? [],$column_arr ?? []);
$mi_remove_arr = array_merge($mi_remove_arr ?? [],$hide_col_arr ?? []);
$search_result_arr = array_reduce($search_result ?? [], function($result, $arr) use($mi_remove_arr) {
$result[] = array_diff_key($arr ?? [],$mi_remove_arr ?? []);
return $result;
}, array());
//FOOTER ARRAY
$footer_col_arr = array_diff($mi_inp_arr ?? [],$mi_remove_arr ?? []);
//for in footer to remove without decimal fields
$footer_col_arr = array_diff($footer_col_arr ?? [],$mi_pick_inp_arr ?? []);
$status = array(1=>"Yes",2=>"No",);
// $front_heads = array('with_esi'=>"With ESI",'with_pf'=>"With PF",);
$esi_heads = array('employee_esi'=>"Employee Esi",'employer_esi'=>"Employer Esi",);
$pf_heads = array('employee_pf'=>"Employee Pf",'employer_pf'=>"Employer Pf",'eps'=>"FPF",);
$static_heads = array('total_earnings'=>"Total Earnings",'total_deductions'=>"Total Deductions",'net_pay'=>"Net Pay",);
// ,$front_heads
// ,$mi_pick_inp_arr
$display_labels = array_merge($column_arr ?? [],$mi_cal_inp_arr ?? [],$esi_heads ?? [],$pf_heads ?? [],$static_heads ?? [],$mi_pick_inp_arr ?? []);
$footer_col_arr = array_merge($footer_col_arr ?? [],$esi_heads ?? [],$pf_heads ?? [],$static_heads ?? []);
$hide_column = ['role' => 'role','date_of_birth' => 'date_of_birth','date_of_joining' => 'date_of_joining','process_month' => 'process_month','termination_status' => 'termination_status','entry_status' => 'entry_status','personal_code' => 'personal_code','project_id' => 'project_id','activity_no' => 'activity_no'];
$esi_sts = array_column($search_result_arr ?? [],'with_esi');
$pf_sts = array_column($search_result_arr ?? [],'with_pf');
$display_labels = array_diff_key($display_labels ?? [], $hide_col_arr ?? []);
//print_r($front_heads); die;
$head_sts = 1;
//Generate table data
$tr_line = "";
// echo "<pre>";
// print_r($pick_list_arr);die;
foreach ($search_result_arr as $key => $value_data){
$tr_line .= "<tr>";
$foot_line = "";
foreach($display_labels as $label_name => $val){
$value = $value_data[$label_name];
$with_esi = (int)$value_data['with_esi'];
$with_pf = (int)$value_data['with_pf'];
// if($label_name === 'with_esi' || $label_name === 'with_pf'){
// $value = $status[$value];
// }
if(!$hide_column[$label_name]){
if($pick_list_arr[$label_name] && $label_name !== 'wbs_element'){ //Fill Picklist Data
$value = $pick_list_arr[$label_name]['array_list'][$value];
//for posiiton field
if($label_name === 'position'){
$gross = " ~ ".$value_data['gross'];
$value = $value.$gross;
}
}
if($table_head_arr[$label_name]){ //Replace View Name
$view_name = $table_head_arr[$label_name];
if($head_sts === 1){
//class name add for only to ear and ded cols
$class = '';
if((int)$ear_ded_col_arr[$label_name] === 2){
$class = "class = 'earn'";
}else
if((int)$ear_ded_col_arr[$label_name] === 3){
$class = "class = 'deduct'";
}
$tr_head .= "<th $class>$view_name</th>";
}
$tr_line .= "<td>$value</td>";
}
/*else
if($front_heads[$label_name]){ //Replace View Name
$view_name = $front_heads[$label_name];
if($head_sts === 1){
$tr_head .= "<th>$view_name</th>";
}
$tr_line .= "<td>$value</td>";
}*/
else
if($static_heads[$label_name]){ //Replace View Name
$view_name = $static_heads[$label_name];
if($head_sts === 1){
$tr_head .= "<th>$view_name</th>";
}
$tr_line .= "<td>$value</td>";
}else
if($esi_heads[$label_name]){ //Replace View Name
if(in_array("1",$esi_sts)){
if($head_sts === 1){
$view_name = $esi_heads[$label_name];
$tr_head .= "<th>$view_name</th>";
}
$tr_line .= "<td>$value</td>";
}
}else
if($pf_heads[$label_name]){ //Replace View Name
if(in_array("1",$pf_sts)){
if($head_sts === 1){
$view_name = $pf_heads[$label_name];
$tr_head .= "<th>$view_name</th>";
}
$tr_line .= "<td>$value</td>";
}
}
if($footer_col_arr[$label_name]){
if($esi_heads[$label_name]){
if(in_array("1",$esi_sts)){
$sum_value = $footer_sum_arr[$label_name];
$foot_line .= "<td>".number_format((float)$sum_value, 2, '.', '')."</td>";
}
}else
if($pf_heads[$label_name]){
if(in_array("1",$pf_sts)){
$sum_value = $footer_sum_arr[$label_name];
$foot_line .= "<td>".number_format((float)$sum_value, 2, '.', '')."</td>";
}
}else{
$sum_value = $footer_sum_arr[$label_name];
$foot_line .= "<td>".number_format((float)$sum_value, 2, '.', '')."</td>";
}
}else{
if($label_name === 'emp_name'){
$foot_line .= "<td style='text-align:right;'>Grand Total :</td>";
}else{
$foot_line .= "<td></td>";
}
}
}
}
$tr_line .= "</tr>";
$head_sts++;
}
//<tfoot><tr style='background-color:#e4e3e3;'>$foot_line</tr></tfoot>
$table_info = "<table class='table table-striped table-hover display' id='calc_table'><thead><tr>$tr_head</tr></thead><tbody>$tr_line <tr style='background-color:#e4e3e3;font-weight:bold;color:blue;'>$foot_line</tr></tbody></table>";
//echo $table_info; die;
echo json_encode(array("success"=>true,"message"=>"Success","table_data"=>$table_info));
}
}
//BSK START FOR PROJECT ID FETCH FROM MI FMS BASED ON PERSONAL CODE
public function project_id_fetch(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$personal_code = $this->input->post('personal_code');
$search_month = $this->input->post('search_month');
$pro_id_qry = 'SELECT pro_id,pro_desc FROM `cw_sap_project` inner join cw_misc_input_fms on cw_misc_input_fms.project_id = cw_sap_project.pro_id where cw_misc_input_fms.process_month = "'.$search_month.'" and cw_misc_input_fms.trans_status = 1 and pro_personal_area_id = "'.$personal_code.'" and cw_misc_input_fms.entry_status = 1 and check_status = 0 GROUP BY cw_sap_project.pro_id';
$pro_id_info = $this->db->query("CALL sp_a_run ('SELECT','$pro_id_qry')");
$pro_id_rslt = $pro_id_info->result_array();
$pro_id_info->next_result();
echo json_encode($pro_id_rslt);
}
//BSK END FOR PROJECT ID FETCH FROM MI FMS BASED ON PERSONAL CODE
//Update Posting Status and Approve reject status
public function update_status(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$search_month = $this->input->post('search_month');
$remarks = $this->input->post('remarks');
$type = $this->input->post('type');
$created_on = date("Y-m-d H:i:s");
if($type === 'approve'){
$qry = 'entry_status=1,check_status = 1,remarks="'.$remarks.'",misc_apr_date = "'.$created_on.'"';
}else{
$qry = 'entry_status=2,check_status = 2,remarks="'.$remarks.'",misc_rej_date = "'.$created_on.'"';
}
$upd_qry = 'UPDATE cw_misc_input_fms SET '.$qry.' where cw_misc_input_fms.project_id = "'.$project.'" and personal_code = "'.$personal_code.'" and cw_misc_input_fms.process_month = "'.$search_month.'" and check_status = 0 and entry_status = 1';
$update_info = $this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
if($update_info){
echo json_encode(array("status" => TRUE,"message"=>"Updated Successfully..."));
}else{
echo json_encode(array("status" => FALSE,"message"=>"Please try After sometime..."));
}
}
}
?>