File: //home/cafsindia/hrms_allyindian_com/application_bk/controllers/Monthly_input.php
<?php
/**********************************************************
Filename: Monthly Input
Description: Monthly Input for adding input.
Author: Jaffer Sathik
Created on: 28 December 2018
Reviewed by: udhayakumar Anandhan
Reviewed on: 09-FEB-2019
Approved by:
Approved on:
-------------------------------------------------------
Modification Details
Changed by:
Change Info:
-------------------------------------------------------
***********************************************************/
if (!defined('BASEPATH'))
exit('No direct script is allowed');
require_once("Action_controller.php");
class Monthly_input extends Action_controller{
public function __construct(){
parent::__construct('monthly_input');
$this->load->model('Process_payroll_model');
if (!$this->Appconfig->isAppvalid()){
redirect('config');
}
$lock_query = 'select GROUP_CONCAT(previous_column) as previous_column from cw_monthly_input_previous inner join cw_form_setting on cw_form_setting.label_name = previous_column where cw_monthly_input_previous.trans_status = 1 and column_status in (1)';
$lock_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_query')");
$lock_result = $lock_data->result();
$lock_data->next_result();
$previous_column = $lock_result[0]->previous_column;
$prev_qry = "";
if($previous_column){
$previous_column = '"'.str_replace(',', '","', $previous_column).'"';
$prev_qry = 'and label_name not in ('.$previous_column.')';
}
$table_query = 'select label_name,view_name,field_type from cw_form_setting where prime_module_id = "employees" and trans_status = "1" and (earn_month_check = "1" OR deduction_month_check = "1") and FIND_IN_SET("'.$this->logged_role.'",field_for) '.$prev_qry.' ORDER BY monthly_input_sort ASC';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$result = $table_info->result();
$table_info->next_result();
$this->table_head_val = $result;
$select_key = array_column($result, "label_name");
$this->mi_select_query = "$this->prime_table.$this->prime_id,";
$this->mi_select_query .= "cw_monthly_input.".implode(",cw_monthly_input.",$select_key ?? []);
$this->mi_select_query .= ",cw_monthly_input.input_status";
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$data['key'] = $this->generateKey();
//GET Monthly Input Columns
$data['table_head'] = $this->table_head_val;
$this->page_info();
$data['master_pick'] = $this->pick_list;
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
// $category_list[""] = "";
foreach ($role_result as $for) {
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$lock_query = 'select previous_column,column_status from cw_monthly_input_previous inner join cw_form_setting on cw_form_setting.label_name = previous_column where cw_monthly_input_previous.trans_status = 1 and column_status in (1,2)';
$lock_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_query')");
$lock_result = $lock_data->result_array();
$lock_data->next_result();
$lock_result = array_reduce($lock_result, function ($result, $arr) {
$result[$arr['previous_column']] = $arr['column_status'];
return $result;
}, array());
$data['lock_result'] = $lock_result;
$data['module_id'] = $this->control_name;
//Add Additional Information from the settings
if($this->company_info[0]->mi_process_type !== 'role' && $this->company_info[0]->mi_process_type !== '0'){
$get_components = 'select pick_list,pick_list_type,view_name,pick_table,label_name from `cw_form_setting` where prime_module_id = "employees" and input_view_type in (1,2) and field_type = 5 and label_name = "'.$this->company_info[0]->mi_process_type.'"';
$get_components_info = $this->db->query("CALL sp_a_run ('SELECT','$get_components')");
$get_components_result = $get_components_info->result();
$get_components_info->next_result();
$pick_table = $get_components_result[0]->pick_table;
$pick_list = $get_components_result[0]->pick_list;
$pick_list_type = (int)$get_components_result[0]->pick_list_type;
$label_name = $get_components_result[0]->label_name;
$view_name = $get_components_result[0]->view_name;
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];
if($pick_display){
$pick_list = "$pick_list_val_1,CONCAT_WS(\" - \", $pick_display) as $pick_list_val_2";
}
$pick_query = "select $pick_list from $pick_table where trans_status = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
if($pick_result){
$pick_key = array_column($pick_result, $pick_list_val_1);
$pick_val = array_column($pick_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
}
// NEWLY ADDED FOR AVON DB ISSUE _ARN 03-11-2025
$final_pick = array("ALL" => "---- Select All ----") + (array)$final_pick;
}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";
$pick_query = "select $pick_list_val_1,$pick_list_val_2 from $pick_table where $pick_list_val_3 = 1 ";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
if($pick_result){
$pick_key = array_column($pick_result, $pick_list_val_1);
$pick_val = array_column($pick_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
}
// NEWLY ADDED FOR AVON DB ISSUE _ARN 03-11-2025
$final_pick = array("ALL" => "---- Select All ----") + (array)$final_pick;
}
$mi_process_type = array("form_label"=>form_label($view_name, 'add_component', array('class' => '')),"form_input"=>form_dropdown(array('name' =>'add_component','id' =>'add_component','class' =>'form-control input-sm select2'),$final_pick));
}else{
$mi_process_type = "";
}
$data['mi_process_type'] = $mi_process_type;
$this->load->view('monthly_input/manage', $data);
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
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);
}
$this->search_info();
$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']);
$search_query = str_replace("@SELECT@",$this->mi_select_query,$this->base_query);
$access_data = $this->session->userdata('access_data');
$tab_name = $this->input->post('tab_name');
$category = $this->input->post('category');
$roles = implode(",", $category ?? []);
$search_month = $this->input->post('search_month');
$process_mode = $this->input->post('process_mode');
$add_component = $this->input->post('add_component');
$add_query = "";
$emp_qry = "";
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" => $roles ,"Search Month" =>$search_month];
if($order_col === "prime_monthly_input_id") {
$order_by = "$this->prime_table.date_of_joining DESC";
}else{
$order_by = "$order_col $order_sor";
}
if($add_component && $add_component !== 'undefined' && $add_component !== 'ALL'){
$add_query = ' and '.$this->prime_table.'.'.$this->company_info[0]->mi_process_type.' = "' . $add_component . '"';
$emp_qry = ' and cw_employees.'.$this->company_info[0]->mi_process_type;
$emp_qry = ' and cw_employees.'.$this->company_info[0]->mi_process_type;
}
$common_search = "";
if($search){
$common_search .= 'and (cw_monthly_input.emp_name like "'.$search.'%" or cw_monthly_input.date_of_joining like "'.date('Y-m-d',strtotime($search)).'%" or cw_monthly_input.employee_code like "'.$search.'%")';
}
if($tab_name === 'view' || $tab_name === 'edit'){
$add_column = "";
$add_qry = "";
if($this->company_info[0]->mi_process_type !== 'role' && $this->company_info[0]->mi_process_type !== ''){
$mi_process_type = $this->company_info[0]->mi_process_type;
$add_qry = " , cw_monthly_input.$mi_process_type = cw_employees.$mi_process_type";
$add_column = ",".$this->company_info[0]->mi_process_type;
}
$status_qry = " and input_status = 0";
if($tab_name === 'edit'){
$status_qry = " and input_status in(0,2)";
}
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
if(count($category) === 0 || !$search_month){
echo json_encode(array("draw" => intval($draw),"recordsTotal" => 0,"recordsFiltered" => 0,"data" => 0));
exit(0);
}
//UPDATE MONTHLY INPUT TABLE CURRENT MONTH RESIGNED EMPLOYEES
$resigned_emp_upd = 'UPDATE cw_monthly_input JOIN cw_employees ON cw_monthly_input.employee_code = cw_employees.employee_code SET cw_monthly_input.termination_status = cw_employees.termination_status,cw_monthly_input.role = cw_employees.role,cw_monthly_input.payroll = cw_employees.payroll '.$add_qry.' WHERE cw_monthly_input.input_status = 0 and cw_monthly_input.process_month = "'.$search_month.'"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Mothly Input Termination Status DATA UPDATE",'');
$this->db->query($resigned_emp_upd);
// CATEGORY BASED INSERT/UPDATE
foreach($category as $key => $role){
$date_search = explode("-", $search_month);
$search_date = $date_search[1] . "-" . $date_search[0] . "-01";
//GET MONTHLY INPUT PROCESSED TEMINATED EMPLOYEES
$fandf_mi_exist_qry = 'select GROUP_CONCAT(employee_code) as fandf_mi_emp_code from cw_monthly_input where trans_status = 1 and cw_monthly_input.role = "' . $role . '" and process_month = "' . $search_month . '" and cw_monthly_input.termination_status = 1 and cw_monthly_input.input_status = 1 and payroll = 1';
$fandf_mi_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$fandf_mi_exist_qry')");
$fandf_mi_exist_rslt = $fandf_mi_exist_data->result();
$fandf_mi_exist_data->next_result();
$fandf_mi_emp_code = $fandf_mi_exist_rslt[0]->fandf_mi_emp_code;
// FANF EMPLOYEE CODES COLLECT
$fandf_mi_emp_code = '"'.implode('","',explode(',', $fandf_mi_emp_code ?? "") ?? []).'"';
//CHECK MONTHLY INPUT EXISTS EMPLOYEES WITHOUT TERMINATED EMPLOYEES
$month_input_exist_qry = 'select employee_code from cw_monthly_input where trans_status = 1 and cw_monthly_input.role = "' . $role . '" and process_month = "' . $search_month . '" and cw_monthly_input.termination_status = 0 and employee_code not in('.$fandf_mi_emp_code.') limit 0,1';
$month_input_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$month_input_exist_qry')");
$month_input_exist_rslt = $month_input_exist_data->result();
$month_input_exist_data->next_result();
$month_input_exist_count = (int) $month_input_exist_data->num_rows();
#GET SALARY MONTHS AND DATES
$salary_days = $this->Process_payroll_model->get_salary_date($role);
if($salary_days){
$day_conditions = $salary_days['day_conditions'];
if($day_conditions === 3){
$sal_start_date = date("Y-m", strtotime("-1 month", strtotime($search_date)))."-".$salary_days['day_start'];
$sal_end_date = date("Y-m-" . $salary_days['day_end'], strtotime($search_date));
}else{
$sal_start_date = date("Y-m-01".$salary_days['day_start'], strtotime($search_date));
if((int)$day_conditions === 2){
$sal_end_date = date("Y-m-".$salary_days['day_end'], strtotime($search_date));
}else{
$sal_end_date = date("Y-m-".$salary_days['day_end'], strtotime($search_date));
}
}
}else{
echo json_encode(array('success' => false,'status' => "Monthday",'message' => "Month days Not Set"));
exit(0);
}
//ALREADY MONTHLY INPUT HAVE SOME ENTRYS GET THAT EXISTS EMPLOYEES FOR ALLOW NEW EMPLOYEES ONLY #BASED ON PROCESS MONTH
$missed_emp_qry = 'select GROUP_CONCAT(employee_code) as employee_codes from cw_employees where cw_employees.trans_status = 1 and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m-%d") <= "' . $sal_end_date . '" and cw_employees.termination_status = 0 and employee_code in(SELECT employee_code from cw_monthly_input WHERE cw_monthly_input.trans_status = 1 and cw_monthly_input.process_month = "' . $search_month . '")';
$missed_emp_data = $this->db->query("CALL sp_a_run ('SELECT','$missed_emp_qry')");
$missed_emp_rslt = $missed_emp_data->result();
$missed_emp_data->next_result();
$employee_codes = '"'.str_replace(',', '","', $missed_emp_rslt[0]->employee_codes).'"';
#PREPARE THE ALREADY EXISTS EMPLOYEES CODES AND FANDF EMPLOYEES CODE
if($fandf_mi_emp_code && $employee_codes){
$employee_codes = $employee_codes.','.$fandf_mi_emp_code;
}else
if($fandf_mi_emp_code){
$employee_codes = $fandf_mi_emp_code;
}
// IF ZERO ENTRYS ON MONTHLY INPUT NEW DATAS INSERTED #
if($month_input_exist_count === 0){
$save_monthly_query = 'INSERT INTO cw_monthly_input(employees_id,role,employee_code,emp_name,termination_status,date_of_joining,process_month,payroll,trans_created_by,trans_created_date '.$add_column.') SELECT prime_employees_id,role,employee_code,emp_name,termination_status,date_of_joining,"' . $search_month . '",payroll,"' . $logged_id . '","' . date("Y-m-d H:i:s") . '" '.$add_column.' FROM cw_employees WHERE cw_employees.trans_status = 1 and cw_employees.role = "' . $role . '" and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m-%d") <= "' . $sal_end_date . '" and cw_employees.termination_status = 0 and cw_employees.employee_code not in('.$employee_codes.')';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Mothly Input DATA ADD",'');
$this->db->query($save_monthly_query);
#END OF INSERT
//CHECKS PREVIOUS MONTH TABE DATA EXIST #START
$previous_column_qry = 'SELECT GROUP_CONCAT(CONCAT("cw_monthly_input.",previous_column, "=cw_transactions.", matching_column)) AS update_columns FROM cw_monthly_input_previous where cw_monthly_input_previous.trans_status=1';
$previous_column_data = $this->db->query("CALL sp_a_run ('SELECT','$previous_column_qry')");
$previous_column_result = $previous_column_data->result();
$previous_column_data->next_result();
$previous_column_count = (int) $previous_column_data->num_rows();
if($previous_column_count > 0){
$update_columns = $previous_column_result[0]->update_columns;
$datestring = "01-$search_month first day of last month";
$dt = date_create($datestring);
$last_month = $dt->format('m-Y');
//CHECKS PREVIOUS MONTH DATA EXISTS
$trans_exist_qry = 'select employee_code from cw_transactions where trans_status = 1 and cw_transactions.role = "' . $role . '" and transactions_month = "' . $last_month . '" and termination_status = 0 limit 0,1';
$trans_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$trans_exist_qry')");
$trans_exist_rslt = $trans_exist_data->result();
$trans_exist_data->next_result();
$trans_exist_count = (int) $trans_exist_data->num_rows();
if($trans_exist_count > 0){
// GET PREVIOUS TRANSACTION DATA OF MATCHING COLUM
if($update_columns){
$get_previous_val_qry = 'UPDATE cw_monthly_input
INNER JOIN cw_transactions ON cw_monthly_input.employee_code = cw_transactions.employee_code
SET ' . $update_columns . ' WHERE cw_transactions.trans_status=1 and cw_transactions.role = "' . $role . '" and cw_transactions.transactions_month = "' . $last_month . '" and cw_monthly_input.process_month = "'.$search_month.'" and cw_monthly_input.input_status in (0,1)';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Mothly Input DATA UPDATE",'');
$this->db->query($get_previous_val_qry);
}
}
}
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Mothly Input UPDATE Supplementary",'');
$this->update_supplymentry($role,$search_month);
}else{
// IF NOT ZERO ENTRYS ALREADY EXISTS SO WITHOUT THAT DATAS ENTRY ON MONTHLY INPUT NEW DATAS INSERTED #
if($missed_emp_rslt){
$save_monthly_query = 'INSERT INTO cw_monthly_input(employees_id,role,employee_code,emp_name,termination_status,date_of_joining,process_month,payroll,trans_created_by,trans_created_date '.$add_column.') SELECT prime_employees_id,role,employee_code,emp_name,termination_status,date_of_joining,"' . $search_month . '",payroll,"' . $logged_id . '","' . date("Y-m-d H:i:s") . '" '.$add_column.' FROM cw_employees WHERE cw_employees.trans_status = 1 and cw_employees.role = "' . $role . '" and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m-%d") <= "' . $sal_end_date . '" and cw_employees.termination_status = 0 and employee_code not in ('.$employee_codes.') ';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Mothly Input New DATA ADD",'');
$this->db->query($save_monthly_query);
//CHECKS PREVIOUS MONTH TABE DATA EXIST #START
$previous_column_qry = 'SELECT GROUP_CONCAT(CONCAT("cw_monthly_input.",previous_column, "=cw_transactions.", matching_column)) AS update_columns FROM cw_monthly_input_previous where cw_monthly_input_previous.trans_status=1';
$previous_column_data = $this->db->query("CALL sp_a_run ('SELECT','$previous_column_qry')");
$previous_column_result = $previous_column_data->result();
$previous_column_data->next_result();
$previous_column_count = (int) $previous_column_data->num_rows();
if($previous_column_count > 0){
$update_columns = $previous_column_result[0]->update_columns;
$datestring = "01-$search_month first day of last month";
$dt = date_create($datestring);
$last_month = $dt->format('m-Y');
// CHECK PREVIOUS MONTH DATA EXIST
$trans_exist_qry = 'select employee_code from cw_transactions where trans_status = 1 and cw_transactions.role = "' . $role . '" and transactions_month = "' . $last_month . '" and employee_code in ('.$employee_codes.') and cw_transactions.fandf = 2 limit 0,1';
$trans_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$trans_exist_qry')");
$trans_exist_rslt = $trans_exist_data->result();
$trans_exist_data->next_result();
$trans_exist_count = (int) $trans_exist_data->num_rows();
if($trans_exist_count > 0){
// GET PREVIOUS TRANSACTION DATA OF MATCHING COLUMN
if($update_columns){
$get_previous_val_qry = 'UPDATE cw_monthly_input INNER JOIN cw_transactions ON cw_monthly_input.employee_code = cw_transactions.employee_code SET ' . $update_columns . ' WHERE cw_transactions.trans_status=1 and cw_transactions.role = "' . $role . '" and cw_transactions.transactions_month = "' . $last_month . '" and cw_monthly_input.process_month = "'.$search_month.'" and cw_monthly_input.input_status in (0,1) ';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Mothly Input DATA UPDATE",'');
$this->db->query($get_previous_val_qry);
}
}
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Mothly Input UPDATE Supplementary",'');
$this->update_supplymentry($role,$search_month);
}
}
}
} #END
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.termination_status = 0 and cw_monthly_input.role in (".$roles.") ".$common_search." $add_query $status_qry order by $order_by";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " 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."\" and cw_monthly_input.termination_status = 0 and cw_monthly_input.role in (".$roles.") ".$common_search." $add_query $status_qry order by $order_by";
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();
if((int)$process_mode === 2 && (int)$num_rows > 0){
if((int)$access_data['monthly_input']['access_delete'] === 1){
$btn_array = array();
foreach($this->table_head_val as $table){
$label_name = $table->label_name;
$view_name = $table->view_name;
$field_type = (int)$table->field_type;
if($field_type === 2){
$btn_array[$label_name] = "<i class='fa fa-remove' title='Delete' style='color:red' id='delete_btn_".$label_name."' onclick=delete_column('".$label_name."','".$tab_name."') aria-hidden='true'></i>";
}else{
$btn_array[$label_name] = "";
}
}
// HOTCODED FOR 0TH ROW COLUMN FOR TABLE REQUIRED _ARN 25-08-2025
$btn_array['prime_monthly_input_id'] = "";
array_unshift($search_result, $btn_array);
}
}
}else
if($tab_name === 'input'){
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" $add_query and cw_monthly_input.role in (".$roles.") and $this->prime_table.fandf = 2 ".$common_search." and cw_monthly_input.termination_status = 0 and $this->prime_table.input_status = 1 and payroll_status = 0 order by $order_by";// fandf [MS 18-10-2024]
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " 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."\" and cw_monthly_input.role in (".$roles.") ".$common_search." and cw_monthly_input.termination_status = 0 $add_query and $this->prime_table.input_status = 1 and payroll_status = 0 and $this->prime_table.fandf = 2 order by $order_by";// fandf [MS 18-10-2024]
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();
if((int)$process_mode === 2 && (int)$num_rows > 0){
if((int)$access_data['monthly_input']['access_delete'] === 1){
$btn_array = array();
foreach($this->table_head_val as $table){
$label_name = $table->label_name;
$view_name = $table->view_name;
$field_type = (int)$table->field_type;
if($field_type === 2){
$btn_array[$label_name] = "<i class='fa fa-remove' title='Delete' style='color:red' id='delete_btn_".$label_name."' onclick=delete_column('".$label_name."','".$tab_name."') aria-hidden='true'></i>";
}else{
$btn_array[$label_name] = "";
}
}
// HOTCODED FOR 0TH ROW COLUMN FOR TABLE REQUIRED _ARN 06-09-2025
$btn_array['prime_monthly_input_id'] = "";
array_unshift($search_result, $btn_array);
}
}
}else{
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role in (".$roles.") $add_query ".$common_search." and $this->prime_table.fandf = 2 and payroll_status = 1 order by $order_by";// fandf [MS 18-10-2024]
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role in (".$roles.") $add_query ".$common_search." and $this->prime_table.fandf = 2 and payroll_status = 1 order by $order_by";// fandf [MS 18-10-2024]
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" => $filtered_count,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
public function save_processed_data(){
$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);
}
$this->save_info();
$category = $this->input->post('category');
$process_month = $this->input->post('process_month');
$add_component = $this->input->post('add_component');
$type = $this->input->post('type');
//TABLE DATA BACKEND UPDATE STARTS
$table_data = json_decode($this->input->post('table_data'), true);
$first_index = reset($table_data);
$keys = array_keys($first_index);
//PAYROLL STATUS CHECK VALIDATION
$mi_qry = 'SELECT payroll_status,prime_monthly_input_id FROM cw_monthly_input WHERE trans_status = 1 AND process_month = "'.$process_month.'"';
$mi_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_qry')");
$mi_result = $mi_data->result();
$mi_data->next_result();
$payroll_rslt = array_column($mi_result,'payroll_status','prime_monthly_input_id');
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" => $category ,"Search Month" =>$process_month];
//SUPPLEMETARY STATUS VALIDATION NOT DONE -> NEED TO WORK, CHECK OUT UPDATE TABLE FUN!
foreach($table_data as $mi_id => $mi_arr){
$update_query = '(';
foreach($mi_arr as $label_name => $label_value){
$update_query .= '"' . $label_value . '",';
}
$update_query = rtrim($update_query, ', ');
$update_query .= ')';
if($payroll_rslt[$mi_id] !== 1){
$update_queries[] = $update_query;
}
}
$mi_data_upd_value = implode(", ", $update_queries ?? []);
$insert_query = "";
$update_query = "";
foreach($keys as $key){
if($key === "prime_id"){
$key = "prime_monthly_input_id";
}
$insert_query .= $key . ",";
$update_query .= $key . "= VALUES(" . $key ."),";
}
$insert_query = rtrim($insert_query, ",");
$update_query = rtrim($update_query, ",");
if($mi_data_upd_value){
$upd_qry = 'INSERT INTO cw_monthly_input('.$insert_query.') VALUES '.$mi_data_upd_value.' ON DUPLICATE KEY UPDATE '.$update_query.',trans_updated_by = VALUES(trans_updated_by),trans_updated_date = VALUES(trans_updated_date)';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Mothly Input DATA ADD AND DATA UPDATE",'');
$this->db->query($upd_qry);
}
//TABLE DATA BACKEND UPDATE ENDS
if($type === 'all'){
$input_status = ' ';
}else{
$input_status = ' and input_status = 2';
}
$mi_query = "";
$payroll_query = "";
if($add_component && $add_component !== 'undefined' && $add_component !== 'ALL'){
$mi_query = ' and '.$this->prime_table.'.'.$this->company_info[0]->mi_process_type.' = "' . $add_component . '"';
$payroll_query = ' and cw_transactions.'.$this->company_info[0]->mi_process_type.' = "' . $add_component . '"';
}
//Check Payroll Exist
$payroll_exist_qry = 'select count(*) as exist_count from cw_transactions inner join cw_monthly_input on cw_monthly_input.employee_code = cw_transactions.employee_code AND cw_monthly_input.process_month = cw_transactions.transactions_month where cw_transactions.trans_status =1 and cw_transactions.role in ('.$category.') and cw_transactions.transactions_month = "'.$process_month.'" and cw_transactions.fandf = 2 and cw_monthly_input.trans_status = 1 and input_status = 2';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('SELECT','$payroll_exist_qry')");
$payroll_exist_rslt = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$exist_count = $payroll_exist_rslt[0]->exist_count;
if((int)$exist_count > 0){
echo json_encode(array("success" => FALSE,'message' => 'Payroll Already Exist This Month'));
exit(0);
}
//get monthly input process employee
$fandf_mi_exist_qry = 'select GROUP_CONCAT(employee_code) as fandf_mi_emp_code from cw_monthly_input where trans_status = 1 and cw_monthly_input.role in ('.$category.') and process_month = "' . $process_month . '" and cw_monthly_input.termination_status = 1 and cw_monthly_input.input_status = 1 and payroll = 1 and fandf = 1';
$fandf_mi_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$fandf_mi_exist_qry')");
$fandf_mi_exist_rslt = $fandf_mi_exist_data->result();
$fandf_mi_exist_data->next_result();
$fandf_mi_emp_code = $fandf_mi_exist_rslt[0]->fandf_mi_emp_code;
$fandf_mi_emp_code = implode('","',explode(',', $fandf_mi_emp_code ?? "") ?? []);
$fandf_qry = '';
if($fandf_mi_emp_code){
$fandf_qry = 'and employee_code not in("'.$fandf_mi_emp_code.'")';
}
$created_on = date("Y-m-d h:i:s");
$upd_query .= 'trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '"';
$upd_query = 'UPDATE ' . $this->prime_table . ' SET input_status = 1,'.$this->prime_table.'.fandf = 2 WHERE role in ('.$category.') and termination_status = 0 and process_month = "'.$process_month.'" '.$fandf_qry.' '.$input_status.' '.$mi_query;// fandf [MS 18-10-2024]
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Mothly Input DATA UPDATE",'user');
$this->db->query($upd_query);
echo json_encode(array('success' => true,'message' => "Updated successfully!!!"));
}
public function check_monthly_input(){
$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);
}
$this->save_info();
$role = $this->input->post('category');
$search_month = $this->input->post('search_month');
$process_mode = $this->input->post('process_mode');
$add_component = $this->input->post('add_component');
$logged_id = $this->session->userdata('logged_id');
$roles = implode(",", $role ?? []);
$today_date = date("Y-m-d h:i:s");
$add_query = "";
if($add_component && $add_component !== 'undefined' && $add_component !== 'ALL'){
$add_query = ' and '.$this->company_info[0]->mi_process_type.' = "' . $add_component . '"';
}
//GET ALL CATEGORY FROM CATEGORY MASTER
$cat_qry = 'select prime_category_id,category_name from cw_category where trans_status =1';
$cat_data = $this->db->query("CALL sp_a_run ('SELECT','$cat_qry')");
$cat_data_result = $cat_data->result();
$cat_data->next_result();
$cat_data = array_column($cat_data_result, 'category_name', 'prime_category_id');
//Chech Employees there in the catgory.
$check_emp_count_qry = 'select count(employee_code) as emp_count from cw_employees where trans_status = 1 and role in (' . $roles . ') '.$add_query;
$check_emp_count_data = $this->db->query("CALL sp_a_run ('SELECT','$check_emp_count_qry')");
$check_emp_count_result = $check_emp_count_data->result();
$check_emp_count_data->next_result();
$emp_count = $check_emp_count_result[0]->emp_count;
if ((int)$emp_count === 0){
echo json_encode(array('success' => false,'status' => "category",'message' => "Employees Not present this category"));
exit(0);
}
$formula_qry = 'SELECT count(distinct formula_for) as formula_count FROM cw_payroll_formula where formula_for in ('.$roles.') and trans_status = 1 and fandf_only = 0';
$formula_data = $this->db->query("CALL sp_a_run ('SELECT','$formula_qry')");
$formula_result = $formula_data->result();
$formula_data->next_result();
$formula_count = $formula_result[0]->formula_count;
if((int)$formula_count !== count($role)){
echo json_encode(array('success' => false,'status' => "category",'message' => "Payroll Formula not Created for one or more category"));
exit(0);
}
//GET EMPLOYEE MASTER EARNINGS AND DEDUCTIONS COLUMN(LABELS) ARRAY
$ear_ded_col_qry = 'SELECT label_name,view_name FROM `cw_form_setting` WHERE cw_form_setting.prime_module_id ="employees" and cw_form_setting.input_for in ("34","49") and cw_form_setting.trans_status = 1';
$ear_ded_col_info = $this->db->query("CALL sp_a_run ('SELECT','$ear_ded_col_qry')");
$ear_ded_col_rslt = $ear_ded_col_info->result_array();
$ear_ded_col_info->next_result();
$ear_ded_col_arr = array_column($ear_ded_col_rslt,'label_name','label_name');
//------------ CATEGORY BASED STATUTORY SETTING CHECKING --------------------------------
$stat_err_col_arr = $this->statutory_setting_validate($ear_ded_col_arr,$role,$cat_data);
if(!empty($stat_err_col_arr)){
echo json_encode($stat_err_col_arr);
exit(0);
}
//------------ CATEGORY BASED GRATUITY SETTING CHECKING --------------------------------
$grat_err_col_arr = $this->gratuity_setting_validate($ear_ded_col_arr,$role,$cat_data);
if(!empty($grat_err_col_arr)){
echo json_encode($grat_err_col_arr);
exit(0);
}
//---------------- PROFESSIONAL TAX VALIDATION CHECK ------------------------
// PROFESSIONAL TAX LOCATION NAME GET FROM THE TAX LOCATION MASTER
$pro_loc_qry = 'select cw_professional_tax_location.prime_professional_tax_location_id as location_id,cw_professional_tax_location.professional_tax_location from cw_professional_tax_location where cw_professional_tax_location.trans_status = 1';
$pro_loc_data = $this->db->query("CALL sp_a_run ('SELECT','$pro_loc_qry')");
$pro_loc_rslt = $pro_loc_data->result_array();
$pro_loc_data->next_result();
$pro_loc_name_arr = array_column($pro_loc_rslt,'professional_tax_location','location_id');
//PROFESSIONAL TAX AND TAX RANGE TABLE JOIN TO GET A TAX LOCATION ID
$pro_tax_qry = 'select cw_professional_tax.prime_professional_tax_id,cw_professional_tax.location from cw_professional_tax inner join cw_professional_tax_tax_range on cw_professional_tax_tax_range.prime_professional_tax_id = cw_professional_tax.prime_professional_tax_id where cw_professional_tax.trans_status = 1 and cw_professional_tax_tax_range.trans_status = 1 GROUP BY cw_professional_tax.location ORDER BY cw_professional_tax.location ASC';
$pro_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$pro_tax_qry')");
$pro_tax_rslt = $pro_tax_data->result_array();
$pro_tax_data->next_result();
$pro_tax_arr = array_column($pro_tax_rslt,'location','prime_professional_tax_id');
//EMPLOYEE BASED TAX LOCATION ID GET
$emp_tax_loc_qry = 'select cw_employees.professional_tax_location from cw_employees where cw_employees.trans_status = 1 and cw_employees.termination_status = 0 and professional_tax_location NOT IN("","0") and cw_employees.role in ('.$roles.') GROUP BY cw_employees.professional_tax_location ORDER BY cw_employees.professional_tax_location ASC';
$emp_tax_loc_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_tax_loc_qry')");
$emp_tax_loc_rslt = $emp_tax_loc_data->result_array();
$emp_tax_loc_data->next_result();
$emp_tax_loc_arr = array_column($emp_tax_loc_rslt,'professional_tax_location','professional_tax_location');
$emp_tax_err_arr = array_diff($emp_tax_loc_arr,$pro_tax_arr);
if(!empty($emp_tax_err_arr)){
$emp_tax_err_data = implode(",",array_map(function($v) use(&$pro_loc_name_arr){
return $pro_loc_name_arr[$v];
}, $emp_tax_err_arr));
echo json_encode(array('success' => FALSE,'message' => "Professional Tax Settings Create for the Location's $emp_tax_err_data"));
exit(0);
}
//------------------ PROFESSIONAL TAX VALIDATION CHECK END ---------------------
//Check Payroll Count
$check_payroll_exit_qry = 'select count(employee_code) as emp_count from cw_transactions where cw_transactions.fandf = 2 and trans_status = 1 and transactions_month="'.$search_month.'" and role in ('.$roles.') '.$add_query;
$check_payroll_data = $this->db->query("CALL sp_a_run ('SELECT','$check_payroll_exit_qry')");
$check_payroll_result = $check_payroll_data->result();
$check_payroll_data->next_result();
$payroll_count = $check_payroll_result[0]->emp_count;
//Check Lock Month
$exist_qry = 'select lock_month from cw_monthly_input_lock where lock_month = "'.$search_month.'" and status = 1 and trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$num_rows = $exist_data->num_rows();
$exist_data->next_result();
if ((int)$num_rows > 0) {
echo json_encode(array('success' => FALSE,'status' => "locked",'message' => "Monthly Input Locked For this Month!!!"));
exit(0);
}else{
if($search_month){
$date_search = explode("-", $search_month);
$search_date = $date_search[1]."-".$date_search[0]."-01";
//Get Month Days
$month_day_qry = 'select count(distinct category) as count from cw_month_day where cw_month_day.trans_status = 1 and category in ('.$roles.')';
$month_day_data = $this->db->query("CALL sp_a_run ('SELECT','$month_day_qry')");
$month_day_result = $month_day_data->result();
$month_day_data->next_result();
$month_day_count = (int)$month_day_result[0]->count;
if($month_day_count !== count($role)) {
echo json_encode(array('success' => false,'status' => "Monthday",'message' => "Month days Not Set for one or more Category.."));
exit(0);
}
//Check monthy input data exist Start
$month_input_exist_qry = 'select employee_code from cw_monthly_input where trans_status = 1 and cw_monthly_input.role in ('.$roles.') and process_month = "' . $search_month . '" and cw_monthly_input.termination_status = 0 and cw_monthly_input.payroll = 1 '.$add_query.' limit 0,1 ';
$month_input_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$month_input_exist_qry')");
$month_input_exist_rslt = $month_input_exist_data->result();
$month_input_exist_data->next_result();
$month_input_exist_count = (int) $month_input_exist_data->num_rows();
if($month_input_exist_count === 0){
echo json_encode(array('success' => true,'message' => "Monthly Input Successfully Saved..."));
}else{
echo json_encode(array('success' => true,'message' => "Monthly Input Already Exist for this Month..."));
}
}
}
}
//DETELE COLUMN
public function delete_column(){
$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);
}
$category = $this->input->post('category');
$roles = implode(",", $category ?? []);
$search_month = $this->input->post('search_month');
$label_name = $this->input->post('label_name');
$created_on = date("Y-m-d H:i:s");
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" => $roles ,"Search Month" =>$search_month];
$payroll_exist_qry = 'select count(*) as exist_count from cw_transactions where cw_transactions.trans_status =1 and transactions_month = "'.$search_month.'"';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('SELECT','$payroll_exist_qry')");
$payroll_exist_rslt = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$exist_count = $payroll_exist_rslt[0]->exist_count;
if((int)$exist_count > 0){
echo json_encode(array("success" => FALSE,'message' => 'Payroll Already Exist for This Month'));
exit(0);
}
$upd_query = 'UPDATE cw_monthly_input SET ' . $label_name . ' = 0,trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '" WHERE cw_monthly_input.role in ('.$roles.') and process_month = "' . $search_month . '" and payroll_status != 1';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Mothly Input Delete Column DATA UPDATE",'user');
if($this->db->query($upd_query)){
echo json_encode(array('success' => true,'message' => "Column Deleted successfully"));
}else{
echo json_encode(array('success' => false,'message' => "Unable to Delete, Please contact admin..!"));
}
}
public function update_table(){
$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);
}
$label_name = $this->input->post('label_name');
$value = $this->input->post('value');
$prime_id = $this->input->post('prime_id');
$hid_doj = date("Y-m-d", strtotime($this->input->post('hid_doj')));
$hid_process_month = date("Y-m-d", strtotime("01-" . $this->input->post('hid_process_month')));
$category = $this->input->post('category');
$process_month = $this->input->post('hid_process_month');
$employee_code = $this->input->post('hid_emp_code');
$tab_name = $this->input->post('tab_name');
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" => $category ,"Search Month" =>$process_month,"Employee Code"=>$employee_code];
$this->column_mapping();
$get_map_column = $this->get_map_arr['paid_days'];
if(!$get_map_column){
echo json_encode(array('success' => False, 'message' => "Column Not Mapped in Module settings. Please map and try aftersometime.."));
exit(0);
}
// LOP CREDIT CHCEK BERFORE CHNAGE MONTHLY INPUT AND FANDF EXISTS IN LOP CREDIT TRANS -ARN 23-09-2025
$lop_credit_qry = 'SELECT SUM(credit_days) as credit_days_sum FROM cw_lop_credit_trans WHERE trans_status = 1
AND process_month = "'.$process_month.'" AND employee_code = "'.$employee_code.'"';
$lop_credit_data = $this->db->query("CALL sp_a_run ('SELECT','$lop_credit_qry')");
$lop_credit_result = $lop_credit_data->result();
$lop_credit_data->next_result();
$lop_credit_sum = (float)$lop_credit_result[0]->credit_days_sum;
if($lop_credit_sum > 0){
echo json_encode(array('success' => false, 'message' => "Lop Credit Already Processed.. Please remove and try again"));
exit(0);
}
if($label_name === $get_map_column){
$paid_day_count = $this->get_total_work_days($category,$process_month);
if($value > $paid_day_count){
echo json_encode(array("success" => FALSE,'message' => 'Paid Days Must Be Equal To Or Lesser Than Month Days'));
exit(0);
}
}
$payroll_exist_qry = 'select count(*) as exist_count from cw_transactions where cw_transactions.trans_status =1 and transactions_month = "'.$process_month.'" and employee_code = "'.$employee_code.'"';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('SELECT','$payroll_exist_qry')");
$payroll_exist_rslt = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$exist_count = $payroll_exist_rslt[0]->exist_count;
if((int)$exist_count > 0){
echo json_encode(array("success" => FALSE,'message' => 'Payroll Already Exist This Month'));
exit(0);
}
if($label_name === "supplementary_status" && (int) $value === 1){
$month_day_qry = 'select category,day_conditions,day_count,day_start,day_end from cw_month_day where cw_month_day.trans_status = 1 and category ="' . $category . '"';
$month_day_data = $this->db->query("CALL sp_a_run ('SELECT','$month_day_qry')");
$month_day_result = $month_day_data->result();
$month_day_data->next_result();
//Get Salary Start Date
if($month_day_result){
$role = $month_day_result[0]->category;
$day_conditions = $month_day_result[0]->day_conditions;
$day_count = $month_day_result[0]->day_count;
$day_start = $month_day_result[0]->day_start;
$day_end = $month_day_result[0]->day_end;
if((int)$day_conditions === 3) {
//As Discussed with jaffer on 23rdJuly2019
$prev_month = date("Y-m-" . $day_start, strtotime("-1 month", strtotime($hid_process_month)));
$end_month = date("Y-m-" . $day_end, strtotime($hid_process_month));
}else{
$sal_start = '01';
// if((int)$day_conditions === 2){
$day_end = date("t");
// }
$prev_month = date("Y-m-" . $sal_start, strtotime($hid_process_month));
$end_month = date("Y-m-" . $day_end, strtotime($hid_process_month));
}
}
$end_date = strtotime($end_month);
$prev_date = strtotime($prev_month);
$mydate = strtotime($hid_doj);
if ($mydate < $prev_date || $mydate > $end_date) {
echo json_encode(array(
'success' => false,
'status' => "DOJ",
'message' => "This Employee not Eligible for Supplementary Process!!!"
));
exit(0);
}
}
if($tab_name === 'edit'){
$input_status = ',input_status = 2';
}else{
$input_status = ',input_status = 1';
}
$created_on = date("Y-m-d h:i:s");
$upd_query .= 'trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '"';
$upd_query = 'UPDATE ' . $this->prime_table . ' SET ' . $label_name . ' = "' . $value . '" '.$input_status.' WHERE ' . $this->prime_id . ' = "' . $prime_id . '"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Mothly Input DATA UPDATE",'user');
$this->db->query($upd_query);
echo json_encode(array('success' => true,'message' => "Updated successfully!!!"
));
}
//IMPORT FILE VIEW INFORMATION
public function import(){
$data['encKey'] = $this->generateKey();
$data['module_id'] = $this->control_name;
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
// $category_list[""] = "---- Select Category ----";
foreach ($role_result as $for) {
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "' . $this->control_name . '" and trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_format_drop[""] = "---- Excel Format ----";
foreach ($excel_result as $excel) {
$prime_excel_format_id = $excel->prime_excel_format_id;
$excel_name = $excel->excel_name;
$excel_format_drop[$prime_excel_format_id] = $excel_name;
}
$data['excel_format_drop'] = $excel_format_drop;
$this->load->view('monthly_input/import', $data);
}
/* ==============================================================*/
/* ============ MONHTLY IMPORT OPERATION - START ================*/
/* ==============================================================*/
//SAVE MONTHLY IMPORT FILE PATH
public function save_import(){
//Encryption
$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);
}
$module_id = $this->input->post('module_id');
$excel_format = $this->input->post('excel_format');
$excel_file_path = $this->input->post('excel_file_path');
$excel_sheet_name = $this->input->post('excel_sheet_name');
$excel_start_row = $this->input->post('excel_start_row');
$excel_end_row = $this->input->post('excel_end_row');
$process_month = $this->input->post('process_month');
$category = $this->input->post('category_id[]');
$roles = implode(",", $category ?? []);
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$allow_left = '';
//Check Validations
$formula_qry = 'SELECT count(distinct formula_for) as formula_count FROM cw_payroll_formula where formula_for in ('.$roles.') and trans_status = 1 and fandf_only = 0';
$formula_data = $this->db->query("CALL sp_a_run ('SELECT','$formula_qry')");
$formula_result = $formula_data->result();
$formula_data->next_result();
$formula_count = $formula_result[0]->formula_count;
if((int)$formula_count !== count($category)){
echo json_encode(array('success' => false,'status' => "category",'message' => "Payroll Formula not Created for one or more category"));
exit(0);
}
$import_query = 'insert into cw_month_import (module_id,excel_format,excel_file_path,excel_sheet_name,excel_start_row,excel_end_row,category,process_month,trans_created_by,trans_created_date) value ("' . $module_id . '","' . $excel_format . '","' . $excel_file_path . '","' . $excel_sheet_name . '","' . $excel_start_row . '","' . $excel_end_row . '","' . $roles . '","' . $process_month . '","' . $logged_id . '","' . $today_date . '")';
$import_info = $this->db->query("CALL sp_a_run ('INSERT','$import_query')");
$import_result = $import_info->result();
$import_info->next_result();
$import_id = $import_result[0]->ins_id;
echo $this->do_monthly_excel_import($import_id,$allow_left);
}
//CUSTOM DELETE IS UPDATED
public function delete_month_input(){
$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);
}
$month_ids = implode(",", $this->input->post('delete_ids') ?? []);
$search_month = $this->input->post('search_month');
$category = $this->input->post('category');
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" =>implode(",", $category) ,"Search Month" =>$search_month];
//Get Emp Counts
$emp_count_qry = 'select GROUP_CONCAT(employee_code separator ",") as emp_code_list from cw_monthly_input where trans_status = 1 and prime_monthly_input_id in (' . $month_ids . ')';
$emp_count_info = $this->db->query("CALL sp_a_run ('RUN','$emp_count_qry')");
$emp_count_result = $emp_count_info->result();
$emp_count_info->next_result();
$emp_code_list = $emp_count_result[0]->emp_code_list;
$emp_code_list = '"'.str_replace(',', '","', $emp_code_list).'"';
//check payroll is count //and role ="' . $category . '"
$payroll_exist_qry = 'select count(*) payroll_count from cw_transactions where trans_status = 1 and cw_transactions.fandf = 2 and transactions_month ="' . $search_month . '" and employee_code in (' . $emp_code_list . ')';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('RUN','$payroll_exist_qry')");
$payroll_exist_result = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$payroll_count = $payroll_exist_result[0]->payroll_count;
if ((int)$payroll_count === 0){
$created_on = date("Y-m-d H:i:s");
$prime_upd_query .= 'trans_deleted_by = "' . $this->logged_id . '",trans_deleted_date = "' . $created_on . '"';
$prime_update_query = 'UPDATE ' . $this->prime_table . ' SET input_status = 0,fandf = 0,' . $prime_upd_query . ' WHERE ' . $this->prime_id . ' in (' . $month_ids . ')';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Mothly Input Delete DATA UPDATE",'user');
$this->db->query($prime_update_query);
echo json_encode(array('success' => TRUE,'message' => "Successfully Deleted"));
} else {
echo json_encode(array(
'success' => FALSE,
'message' => "Payroll Already Exist Please delete the payroll for this month $search_month"
));
}
}
//check supplymentry proces 17 APR 2020
/* public function check_supplymentry(){
$category = $this->input->post('category');
$search_month = $this->input->post('search_month');
$month_input_exist_qry = 'select employee_code from cw_monthly_input where trans_status = 1 and role = "' . $category . '" and process_month = "' . $search_month . '" limit 0,1';
$month_input_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$month_input_exist_qry')");
$month_input_exist_rslt = $month_input_exist_data->result();
$month_input_exist_data->next_result();
$month_input_exist_count = (int) $month_input_exist_data->num_rows();
if ($month_input_exist_count == 0) {
$supplymentry_ext_qry = 'select count(*) supply_count from cw_supplementary_detail where trans_status = 1 and supplementary_month ="' . $search_month . '" and category ="' . $category . '"';
$supplymentry_ext_info = $this->db->query("CALL sp_a_run ('RUN','$supplymentry_ext_qry')");
$supplymentry_ext_result = $supplymentry_ext_info->result();
$supplymentry_ext_info->next_result();
$supply_count = $supplymentry_ext_result[0]->supply_count;
if ((int) $supply_count === 0) {
echo json_encode(array(
'success' => TRUE,
'message' => "Supplementary Not Processed?"
));
} else {
echo json_encode(array(
'success' => FALSE,
'message' => "Ok Processed"
));
}
} else {
echo json_encode(array(
'success' => FALSE,
'message' => "Ok Processed"
));
}
}*/
/* public function process_month_check(){
$process_month = $this->input->post('process_month');
$category = implode(",",$this->input->post('category'));
$mi_exit_qry = 'select count(employee_code) as emp_count from cw_monthly_input where trans_status = 1 and process_month="'.$process_month.'" and termination_status = 0 and role in ('.$category.')';
$mi_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_exit_qry')");
$mi_result = $mi_data->result();
$mi_data->next_result();
$mi_count = $mi_result[0]->emp_count;
if((int)$mi_count > 0){
$check_payroll_exit_qry = 'select count(employee_code) as emp_count from cw_transactions where trans_status = 1 and transactions_month="'.$process_month.'" and fandf != 1 and role in ('.$category.')';
$check_payroll_data = $this->db->query("CALL sp_a_run ('SELECT','$check_payroll_exit_qry')");
$check_payroll_result = $check_payroll_data->result();
$check_payroll_data->next_result();
$payroll_count = $check_payroll_result[0]->emp_count;
if((int)$payroll_count === 0){
$mi_lock_qry = 'select lock_month from cw_monthly_input_lock where lock_month = "'.$process_month.'" and status = 1 and trans_status = 1';
$mi_lock_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_lock_qry')");
$mi_lock_result = $mi_lock_data->result();
$mi_lock_count = $mi_lock_data->num_rows();
$mi_lock_data->next_result();
if((int)$mi_lock_count === 0){
echo json_encode(array('success' => TRUE, 'message' => "Ok Processed"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Monthly Input is already locked?"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Payroll already exit?"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Please generate the monthly input first in monthly input screen?"));
}
}*/
public function update_supplymentry($role,$search_month){
$back_month_date = "01-".$search_month;
$back_month = date("m-Y", strtotime("-1 month", strtotime($back_month_date)));
$supplymentry_sts_qry = 'select count(*) as rslt_count from cw_monthly_input where process_month="'.$back_month.'" and supplementary_status = 1 and trans_status = 1';
$supplymentry_sts_data = $this->db->query("CALL sp_a_run ('SELECT','$supplymentry_sts_qry')");
$supplymentry_sts_result = $supplymentry_sts_data->result();
$supplymentry_sts_data->next_result();
$supply_mi_count = $supplymentry_sts_result[0]->rslt_count;
$supplymentry_exit_qry = 'select count(*) as supply_count from cw_supplementary_detail where supplementary_month = "'.$search_month.'" and category = "'.$role.'" and trans_status = 1';
$supplymentry_data = $this->db->query("CALL sp_a_run ('SELECT','$supplymentry_exit_qry')");
$supplymentry_result = $supplymentry_data->result();
$supplymentry_data->next_result();
$supply_exit_count = $supplymentry_result[0]->supply_count;
if((int)$supply_mi_count > 0){
if((int)$supply_exit_count > 0){
$get_supplymentry_qry = 'select IFNULL(sum(supp_month_days),0) as supp_month_days,IFNULL(sum(supp_paid_days),0) as supp_paid_days,emp_code from cw_supplementary_detail inner join cw_monthly_input on cw_monthly_input.employee_code =cw_supplementary_detail.emp_code where cw_monthly_input.trans_status=1 and cw_supplementary_detail.supplementary_month="'.$search_month.'" and category = "'.$role.'" and supplementary_status = 1 group by emp_code';
$get_supplymentry_data = $this->db->query("CALL sp_a_run ('SELECT','$get_supplymentry_qry')");
$get_supplymentry_result = $get_supplymentry_data->result();
$get_supplymentry_data->next_result();
$update_on = date("Y-m-d H:i:s");
if(!empty($get_supplymentry_result)){
foreach($get_supplymentry_result as $sup_rslt){
$emp_code = $sup_rslt->emp_code;
$supp_paid_days = $sup_rslt->supp_paid_days;
$supp_month_days = $sup_rslt->supp_month_days;
$s_pd_column = $this->get_map_arr['supp_paid_days'];
$s_md_column = $this->get_map_arr['supp_month_days'];
$upd_mi_query = 'UPDATE cw_monthly_input SET '.$s_pd_column.' ="'.$supp_paid_days.'", '.$s_md_column.' ="'.$supp_month_days.'", trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$update_on.'" WHERE employee_code = "'.$emp_code.'" and process_month="'.$search_month.'" and trans_status=1';
$this->db->query($upd_mi_query);
}
}
}
}
return true;
}
}
?>