File: /home/cafsindia/hrms_cafsinfotech_in/application_bk14FEB2026/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);
}
$final_pick = array("ALL" => "---- Select All ----") + $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);
}
//$all_option = array("ALL" => "--- Select All ---");
$final_pick = array("ALL" => "---- Select All ----") + $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' => 'Invalid Request..'));
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 = "";
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);
}
// $cur_month_year = date('Y-m');
//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.payroll = cw_employees.payroll '.$add_qry.' WHERE cw_monthly_input.input_status = 0 and cw_monthly_input.process_month = "'.$search_month.'"';
$update_resined_emp = $this->db->query("CALL sp_a_run ('UPDATE','$resigned_emp_upd')");
foreach ($category as $key => $role){ //Insert data for each role -START
$date_search = explode("-", $search_month);
$search_date = $date_search[1] . "-" . $date_search[0] . "-01";
//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 = "' . $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;
// if($fandf_mi_emp_code){
$fandf_mi_emp_code = '"'.implode('","',explode(',', $fandf_mi_emp_code ?? "") ?? []).'"';
// }
//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 = "' . $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();
//Check monthy input data exist END
//Get Month Days
$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-t".$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);
}
// Zero Entry in monthly input then insert data Start
if($month_input_exist_count === 0){
//or cw_employees.last_working_date >= "'.$sal_start_date.'")
$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('.$fandf_mi_emp_code.')';
$save_monthly_input = $this->db->query("CALL sp_a_run ('INSERT','$save_monthly_query')");
$save_monthly_input_result = $save_monthly_input->result();
$save_monthly_input->next_result();
// Zero Entry in monthly input then insert data END
//Check 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 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 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)';
$update_previous_val_qry = $this->db->query("CALL sp_a_run ('UPDATE','$get_previous_val_qry')");
}
}
}
$ret_rslt = $this->update_supplymentry($role,$search_month);
}else{
//or DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") >= "' . $sal_start_date . '")
//Insert and Update missing employees or new joiners
$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 role = "' . $role . '" 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 . '" and role = "' . $role . '")';
$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).'"';
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($missed_emp_rslt){
//or cw_employees.last_working_date >= "'.$sal_start_date.'")
$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.') ';
$save_monthly_input = $this->db->query("CALL sp_a_run ('INSERT','$save_monthly_query')");
$save_monthly_input_result = $save_monthly_input->result();
$save_monthly_input->next_result();
//Check 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) ';
$update_previous_val_qry = $this->db->query("CALL sp_a_run ('UPDATE','$get_previous_val_qry')");
}
}
$ret_rslt = $this->update_supplymentry($role,$search_month);
}
}
}
} //Insert data for each role -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] = "";
}
}
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] = "";
}
}
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" => $num_rows,"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' => 'Invalid Request..','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');
//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)';
$upd_info = $this->db->query("CALL sp_a_run ('RUN','$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]
$this->db->query("CALL sp_a_run ('UPDATE','$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' => 'Invalid Request..','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' => 'Invalid Request..','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");
$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';
if ($this->db->query("CALL sp_a_run ('RUN','$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' => 'Invalid Request..','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');
$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);
}
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 . '"';
$this->db->query("CALL sp_a_run ('UPDATE','$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' => 'Invalid Request..','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");
//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);
}
//CATEGORY BASED STATUTORY SETTINGS VALIDATE FUNCTION
public function statutory_setting_validate($ear_ded_col_arr,$category_arr,$cat_data){
$rslt_array = array();
//GET CATEGORY BASED STATUTORY SETTINGS ARRAY
$statuory_set_qry = 'select prime_statutory_id,category,esi_eligibilit_formula from cw_statutory where cw_statutory.trans_status = 1';
$statuory_set_data = $this->db->query("CALL sp_a_run ('SELECT','$statuory_set_qry')");
$statuory_set_rslt = $statuory_set_data->result_array();
$statuory_set_data->next_result();
$statuory_err_data = $this->cat_settings_err($statuory_set_rslt,$category_arr,$cat_data);
if($statuory_err_data){
$rslt_array = array('success' => FALSE, 'message' => "Please Map Statutory Settings for the Category of $statuory_err_data");
return $rslt_array;
}else{
//CATEGORY BASED STATUATORY FORMULA COLUMNS CHECKING(MATCHING)
$stat_cat_col_arr = array_column($statuory_set_rslt,'esi_eligibilit_formula','category');
//FUNCTION CALL
$stat_label_col_arr = $this->formula_label_match($stat_cat_col_arr,$ear_ded_col_arr,$cat_data);
$cat_err_name = $stat_label_col_arr['cat_err_name'];
$lable_err_name = $stat_label_col_arr['lable_err_name'];
if($cat_err_name && $lable_err_name){
$rslt_array = array('success' => FALSE,'message' => "Unknown Column's $lable_err_name are mapped for the Category's $cat_err_name in Statutory Settings Module.!");
return $rslt_array;
}else{
return $rslt_array;
}
}
}
//CATEGORY BASED GRATUITY SETTINGS VALIDATE FUNCTION
public function gratuity_setting_validate($ear_ded_col_arr,$category_arr,$cat_data){
$rslt_array = array();
//GET CATEGORY BASED GRATUITY SETTINGS ARRAY
$gratuity_set_qry = 'select prime_gratuity_id,category,formula_detail from cw_gratuity where cw_gratuity.trans_status = 1';
$gratuity_set_data = $this->db->query("CALL sp_a_run ('SELECT','$gratuity_set_qry')");
$gratuity_set_rslt = $gratuity_set_data->result_array();
$gratuity_set_data->next_result();
$gratuity_err_data = $this->cat_settings_err($gratuity_set_rslt,$category_arr,$cat_data);
if($gratuity_err_data){
$rslt_array = array('success' => FALSE, 'message' => "Please Map Gratuity Settings for the Category of $gratuity_err_data");
return $rslt_array;
}else{
//CATEGORY BASED GRATUITY FORMULA COLUMNS CHECKING(MATCHING)
$grat_cat_col_arr = array_column($gratuity_set_rslt,'formula_detail','category');
//FUNCTION CALL
$grat_label_col_arr = $this->formula_label_match($grat_cat_col_arr,$ear_ded_col_arr,$cat_data);
$cat_err_name = $grat_label_col_arr['cat_err_name'];
$lable_err_name = $grat_label_col_arr['lable_err_name'];
if($cat_err_name && $lable_err_name){
$rslt_array = array('success' => FALSE,'message' => "Unknown Column's $lable_err_name are mapped for the Category's $cat_err_name in Gratuity Settings Module.!");
return $rslt_array;
}else{
return $rslt_array;
}
}
}
//FUNCTION FOR CHECK A CATEGORY ADDED OR NOT IN STATUTORY AND GRATUITY SETTINGS MODULE
public function cat_settings_err($cat_set_rslt,$category_arr,$cat_data){
$cat_set_err_data = "";
$cat_set_arr = array_column($cat_set_rslt, 'category');
$cat_set_err_arr = array_diff($category_arr,$cat_set_arr);
if(!empty($cat_set_err_arr)){
$cat_set_err_data = implode(",",array_map(function($v) use(&$cat_data){
return $cat_data[$v];
}, $cat_set_err_arr) ?? []);
}
return $cat_set_err_data;
}
//FUNCTION FOR CHECK AND MATCH A STATUTORY AND GRATUITY FORMULA COLUMNS BASED ON EARNINGS AND DEDUCTIONS
public function formula_label_match($cat_formula_col_arr,$ear_ded_col_arr,$cat_data){
$cat_err_name = '';
$lable_err_name = '';
foreach($cat_formula_col_arr as $cat_key => $label_val){
$preg_match = preg_match_all('#\@(.*?)\@#', $label_val, $match);
$error = 0;
foreach($match[1] as $for_rslt){
if($for_rslt){
if (!in_array($for_rslt, $ear_ded_col_arr)){
$lable_err_name .= $for_rslt.",";
$error = 1;
}
}
}
//Category Name get only once from a row so
if($cat_data[$cat_key] && $error === 1){
$cat_err_name .= $cat_data[$cat_key].",";
}
}
if($cat_err_name && $lable_err_name){
$cat_err_name = rtrim($cat_err_name, ",");
$lable_err_name = rtrim($lable_err_name, ",");
}
return array("cat_err_name" => $cat_err_name,"lable_err_name" => $lable_err_name);
}
//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' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$month_ids = implode(",", $this->input->post('delete_ids') ?? []);
$search_month = $this->input->post('search_month');
$category = $this->input->post('category');
//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 . ')';
$this->db->query("CALL sp_a_run ('UPDATE','$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("CALL sp_a_run ('UPDATE','$upd_mi_query')");
}
}
}
}
return true;
}
}
?>