File: //home/cafsindia/hrms_allyindian_com/application_bk/controllers/Fandf_monthly_inputBK09Dec2025.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 Fandf_monthly_input extends Action_controller{
public function __construct(){
parent::__construct('fandf_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->prime_table = "cw_monthly_input";
$this->prime_id = "prime_monthly_input_id";
$this->mi_select_query = "cw_monthly_input.prime_monthly_input_id,";
$this->mi_select_query .= "cw_monthly_input.".implode(",cw_monthly_input.",$select_key);
}
// 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[""] = "---- 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;
$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;
$data['allow_left_emp'] = $this->company_info[0]->allow_left_emp;
$this->load->view('fandf_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');
$allow_left = (int)$this->input->post('allow_left');
$add_query = "";
$add_emp_query = "";
$emp_qry = "";
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" => $roles, "Search Month" =>$search_month];
if($add_component && $add_component !== 'undefined' && $add_component !== 'ALL'){
$add_query = ' and '.$this->prime_table.'.'.$this->company_info[0]->mi_process_type.' = "' . $add_component . '"';
$add_emp_query = ' and cw_employees.'.$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 = "";
if($this->company_info[0]->mi_process_type !== 'role' && $this->company_info[0]->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($roles === ""){
echo json_encode(array("draw" => intval($draw),"recordsTotal" => 0,"recordsFiltered" => 0,"data" => 0));
exit(0);
}
if($search_month){
$date_search = explode("-", $search_month);
$search_date = $date_search[1] . "-" . $date_search[0] . "-01";
$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 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,"Fandf Mothly Input Termination Status DATA UPDATE",'');
$this->db->query($resigned_emp_upd);
//get monthly input process employee
// and cw_monthly_input.input_status = 1 and payroll = 1
$mi_exist_qry = 'select GROUP_CONCAT(employee_code) as mi_employee_code from cw_monthly_input where trans_status = 1 and cw_monthly_input.role in (' . $roles . ') and payroll = 1 and process_month = "' . $search_month . '"'; #and cw_monthly_input.input_status = 1
$mi_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_exist_qry')");
$mi_exist_rslt = $mi_exist_data->result();
$mi_exist_data->next_result();
$mi_employee_code = $mi_exist_rslt[0]->mi_employee_code;
$mi_employee_code = '"' . implode('","', explode(',', $mi_employee_code)) . '"';
//Check monthy input data exist Start
//and cw_monthly_input.termination_status = 1 and cw_monthly_input.fandf =1 and cw_monthly_input.payroll = 1
$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 employee_code not in('.$mi_employee_code.') '.$add_query.' limit 0,1';
$month_input_exist_data = $this->db->query($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($roles);
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-".$salary_days['day_start'], strtotime($search_date));
//$sal_end_date = date("Y-m-".$salary_days['day_end'], strtotime($search_date));
// if($day_conditions === 2){
$sal_start_date = date("Y-m-01", strtotime($search_date));
$sal_end_date = date("Y-m-t", strtotime($search_date));
//}
}
$bef_six_month = date('Y-m-d', strtotime('-6 month', strtotime($sal_end_date)));
}else{
echo json_encode(array('success' => false,'status' => "Monthday",'message' => "Month days Not Set"));
exit(0);
}
//Insert Prev Month Left Employee Also
if($allow_left === 1){
$leftQry = ' and DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") < "' . $sal_end_date . '" and DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") > "' . $bef_six_month . '" ';
$status_qry .= ' and DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") < "' . $sal_end_date . '" and DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") > "' . $bef_six_month . '" ';
}else{
$leftQry = ' and DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") between "' . $sal_start_date . '" and "' . $sal_end_date . '" ';
$status_qry .= ' and DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") between "' . $sal_start_date . '" and "' . $sal_end_date . '" ';
}
//and (cw_employees.termination_status = 0 or cw_employees.resignation_date >= "'.$sal_start_date.'")
// Zero Entry in monthly input then insert data Start
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 '.$add_column.') SELECT prime_employees_id,role,employee_code,emp_name,termination_status,date_of_joining,"' . $search_month . '",payroll '.$add_column.' FROM cw_employees WHERE cw_employees.trans_status = 1 and termination_status=1 and cw_employees.payroll = 1 and cw_employees.role in (' . $roles . ') '.$emp_qry.' '.$leftQry.' and cw_employees.employee_code not in('.$mi_employee_code.') '.$add_emp_query;
// SESSION SET AUDIT LOG _ARN 10-10-20255
$this->session_setter($filter_keys,"Fandf Mothly Input DATA ADD",'');
$save_fandf_monthly_input = $this->db->query($save_monthly_query);
if($allow_left === 0){
//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 ('RUN','$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 in (' . $roles . ') and transactions_month = "' . $last_month . '" and termination_status = 1 and fandf = 1 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 in ('. $roles . ') and cw_transactions.transactions_month = "' . $last_month . '"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Fandf Mothly Input DATA UPDATE",'');
$update_previous_val_qry = $this->db->query($get_previous_val_qry);
}
}
}
//Calculate Gratuity Amount - BSK
$gratuity_rslt = $this->get_gratuity_amt($roles,$search_month,$sal_start_date,$filter_keys);
if($gratuity_rslt){
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Fandf Mothly Input Supplementary DATA UPDATE",'');
$this->update_supplymentry($roles,$search_month);
}
}
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_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 = 1 and cw_monthly_input.role in ($roles) ".$common_search." and cw_monthly_input.termination_status= 1 $add_query $status_qry order by $this->prime_table.date_of_joining DESC";
$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=1 and cw_monthly_input.role in ($roles) ".$common_search." $add_query $status_qry order by $this->prime_table.date_of_joining DESC";
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();
}else{
//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 = 1 or DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") >= "' . $sal_start_date . '") and role in (' . $roles . ') 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 cw_monthly_input.termination_status = 1 and role in (' . $roles . ')) '.$add_emp_query;
$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($mi_employee_code && $employee_codes){
$employee_codes = $employee_codes.','.$mi_employee_code;
}else
if($mi_employee_code){
$employee_codes = $mi_employee_code;
}
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 '.$add_column.') SELECT prime_employees_id,role,employee_code,emp_name,termination_status,date_of_joining,"' . $search_month . '",payroll '.$add_column.' FROM cw_employees WHERE cw_employees.trans_status = 1 and cw_employees.payroll = 1 and cw_employees.role in (' . $roles . ') '.$emp_qry.' and DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") between "' . $sal_start_date . '" and "' . $sal_end_date . '" and employee_code not in ('.$employee_codes.') '.$add_emp_query;
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Fandf Mothly Input New DATA ADD",'');
$save_monthly_input = $this->db->query($save_monthly_query);
//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 in (' . $roles . ') and transactions_month = "' . $last_month . '" and employee_code in ('.$employee_codes.') and termination_status = 1 and fandf = 1 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 in (' . $roles . ') and cw_transactions.transactions_month = "' . $last_month . '" and cw_transactions.employee_code in ('.$employee_codes.')';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Fandf Mothly Input DATA UPDATE",'');
$update_previous_val_qry = $this->db->query($get_previous_val_qry);
}
}
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Fandf Mothly Input Supplementary DATA UPDATE",'');
$this->update_supplymentry($roles,$search_month);
}
}
//Calculate Gratuity Amount - BSK
$gratuity_rslt = $this->get_gratuity_amt($roles,$search_month,$sal_start_date,$filter_keys);
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_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 = 1 and cw_monthly_input.role in ($roles) ".$common_search." and cw_monthly_input.termination_status= 1 $add_query $status_qry order by $this->prime_table.date_of_joining DESC";
$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 cw_monthly_input.termination_status= 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.termination_status = 1 and cw_monthly_input.role in ($roles) ".$common_search." $add_query $status_qry order by $this->prime_table.date_of_joining DESC";
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($label_name === "employee_code" || $label_name === "emp_name" || $label_name === "process_month" || $label_name === "date_of_joining" || $label_name === "role"|| $label_name === $this->company_info[0]->mi_process_type){
$btn_array[$label_name] = "";
}else{
$btn_array[$label_name] = "<a class='btn btn-xs btn-danger' id='delete_btn_".$label_name."' onclick=delete_column('".$label_name."','".$tab_name."')>delete</a>";
}
}
// 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) ".$common_search." and $this->prime_table.fandf = 1 and $this->prime_table.input_status = 1 and payroll_status = 0 order by $this->prime_table.date_of_joining DESC";// fandf [MS 19-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." $add_query and $this->prime_table.input_status = 1 and $this->prime_table.fandf = 1 and payroll_status = 0 order by $this->prime_table.date_of_joining DESC";// fandf [MS 19-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($label_name === "employee_code" || $label_name === "emp_name" || $label_name === "process_month" || $label_name === "date_of_joining" || $label_name === "role" || $label_name === $this->company_info[0]->mi_process_type){
$btn_array[$label_name] = "";
}else{
$btn_array[$label_name] = "<a class='btn btn-xs btn-danger' id='delete_btn_".$label_name."' onclick=delete_column('".$label_name."','".$tab_name."')>delete</a>";
}
}
// 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{
$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 = 1 and payroll_status = 1 order by $this->prime_table.date_of_joining DESC"; // fandf [MS 19-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 = 1 and payroll_status = 1 order by $this->prime_table.date_of_joining DESC"; // fandf [MS 19-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));
}
//get gratuity details
public function get_gratuity_amt($roles,$search_month,$sal_start_date,$filter_keys){
//get gratuity settings
$check_eligibilty_qry = 'select cw_employees.employee_code,REPLACE(formula_detail,"@","") as formula_detail,cw_employees.date_of_joining,cw_employees.last_working_date,working_days,number_of_years,pay_days,year_rounding,formula_rounding,DATEDIFF(last_working_date, cw_employees.date_of_joining)/365 AS tot_years from cw_gratuity inner join cw_monthly_input on cw_monthly_input.role = cw_gratuity.category inner join cw_employees on cw_employees.employee_code = cw_monthly_input.employee_code where cw_gratuity.trans_status = 1 and cw_gratuity.category in ( '.$roles.') and cw_employees.termination_status = 1 and last_working_date > "'.$sal_start_date.'"';
// echo $check_eligibilty_qry; die;
$check_eligibilty_data = $this->db->query("CALL sp_a_run ('SELECT','$check_eligibilty_qry')");
$check_eligibilty_result = $check_eligibilty_data->result();
$check_eligibilty_data->next_result();
if($check_eligibilty_result){
$prime_ins_query_value = "";
$year_rounding_list = array();
foreach ($check_eligibilty_result as $key => $eligibilty_result) {
$working_days = $eligibilty_result->working_days;
$number_of_years = $eligibilty_result->number_of_years;
$pay_days = $eligibilty_result->pay_days;
$year_rounding = $eligibilty_result->year_rounding;
$gratuity_formula = $eligibilty_result->formula_detail;
$formula_rounding = $eligibilty_result->formula_rounding;
$employee_code = $eligibilty_result->employee_code;
$tot_year = $eligibilty_result->tot_years;
$gratuity_formula = '(('.$gratuity_formula.' * tbl_gratuity_temp.pay_days)/tbl_gratuity_temp.working_days)*tbl_gratuity_temp.tot_year';
//year rounding base working year is rounded
if($year_rounding){
$year_rounding_list = array(1=>$tot_year,2=>round($tot_year),3=>ceil($tot_year),4=>floor($tot_year));
if((int)$tot_year >= 5){
$tot_year = $year_rounding_list[$year_rounding];
}
}
$prime_ins_query_value .= '("'.$employee_code.'","'.$pay_days.'","'.$working_days.'",'.'"'.$tot_year.'",'.'"'.$number_of_years.'"),';
}
//Drop and create Temp Table
$drop_qry = 'DROP TABLE IF EXISTS tbl_gratuity_temp';
$drop_info = $this->db->query("CALL sp_a_run ('RUN','$drop_qry')");
$temp_qry = 'CREATE TABLE tbl_gratuity_temp (employee_code varchar(100),pay_days decimal(15,2),working_days decimal(15,2),tot_year decimal(15,2),number_of_years decimal(15,2))';
$temp_info = $this->db->query("CALL sp_a_run ('RUN','$temp_qry')");
//Insert Data into temp table
$insert_qry = 'INSERT INTO tbl_gratuity_temp(employee_code,pay_days,working_days,tot_year,number_of_years) VALUES '.rtrim($prime_ins_query_value,",");
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Tbl Gratuity Temp DATA ADD",'');
$insert_info = $this->db->query($insert_qry);
$update_qry = 'UPDATE cw_monthly_input INNER JOIN tbl_gratuity_temp ON tbl_gratuity_temp.employee_code = cw_monthly_input.employee_code inner join cw_employees on cw_employees.employee_code = tbl_gratuity_temp.employee_code SET cw_monthly_input.gratuity = IF(tot_year < number_of_years,0,ROUND('. $gratuity_formula.',2)) where cw_monthly_input.process_month = "'.$search_month.'" and input_status = 0';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Fandf Monthly Input DATA UPDATE",'');
$update_info = $this->db->query($update_qry);
return true;
}else{
return true;
}
}
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');
if(!is_array($category)){
$category = [$category];
}
$roles = implode(",", $category);
$roles = implode(",", $category ?? []);
$process_month = $this->input->post('process_month');
$add_component = $this->input->post('add_component');
$type = $this->input->post('type');
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" => $roles, "Search Month" =>$process_month];
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 where cw_transactions.trans_status =1 and cw_transactions.role = "'.$category.'" and cw_transactions.transactions_month = "'.$process_month.'" 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
$mi_exist_qry = 'select GROUP_CONCAT(employee_code) as mi_employee_code from cw_monthly_input where trans_status = 1 and cw_monthly_input.role in (' . $roles . ') and process_month = "' . $process_month . '" and cw_monthly_input.termination_status = 1 and cw_monthly_input.input_status = 1 and payroll = 1 and fandf = 2';
$mi_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_exist_qry')");
$mi_exist_rslt = $mi_exist_data->result();
$mi_exist_data->next_result();
$mi_employee_code = $mi_exist_rslt[0]->mi_employee_code;
$mi_employee_code = implode('","',explode(',', $mi_employee_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,fandf = 1 WHERE role in (' . $roles . ') and process_month = "'.$process_month.'" and employee_code not in("'.$mi_employee_code.'") and termination_status = 1 '.$input_status.' '.$mi_query;
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Fandf 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');
$roles = implode(",", $role ?? []);
$search_month = $this->input->post('search_month');
$process_mode = $this->input->post('process_mode');
$add_component = $this->input->post('add_component');
$allow_left = (int)$this->input->post('allow_left');
$logged_id = $this->session->userdata('logged_id');
$date_search = explode("-", $search_month);
$search_date = $date_search[1] . "-" . $date_search[0] . "-01";
$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 . '"';
}
$salary_days = $this->Process_payroll_model->get_salary_date($roles);
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-".$salary_days['day_start'], strtotime($search_date));
$sal_end_date = date("Y-m-".$salary_days['day_end'], strtotime($search_date));
//if($day_conditions === 2){
$sal_end_date = date("Y-m-t", strtotime($search_date));
// }
}
$bef_six_month = date('Y-m-d', strtotime('-6 month', strtotime($sal_end_date)));
}else{
echo json_encode(array('success' => false,'status' => "Monthday",'message' => "Month days Not Set"));
exit(0);
}
if($allow_left === 1){
$qry = ' and DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") < "' . $sal_start_date . '" and DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") > "' . $bef_six_month . '" ';
}else{
$qry = ' and DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") between "' . $sal_start_date . '" and "' . $sal_end_date . '"';
}
//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 termination_status = 1 '.$qry.' 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' => "No Employee Seperated in this category"));
exit(0);
}
$formula_qry = 'SELECT count(*) as formula_count FROM cw_payroll_formula where formula_for in (' . $roles . ') and trans_status = 1 and fandf_only = 0 order by prime_payroll_id limit 0,1';
$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 === 0){
echo json_encode(array('success' => false,'status' => "category",'message' => "Payroll Formula not Created for this category"));
exit(0);
}
$check_eligibilty_qry = 'select working_days,number_of_years,pay_days,year_rounding,formula_detail,formula_rounding from cw_gratuity where trans_status = 1 and category in (' . $roles . ')';
$check_eligibilty_data = $this->db->query("CALL sp_a_run ('SELECT','$check_eligibilty_qry')");
$check_eligibilty_result = $check_eligibilty_data->result();
$check_eligibilty_data->next_result();
$gratuity_formula = $check_eligibilty_result[0]->formula_detail;
if (!$gratuity_formula) {
echo json_encode(array('success' => false,'status' => "gratuity",'message' => "Gratuity setting is not done!!!"));
exit(0);
}
/* //Check Payroll Count
$check_payroll_exit_qry = 'select count(employee_code) as emp_count from cw_transactions where termination_status = 1 and trans_status = 1 and transactions_month="' . $search_month . '" and role="' . $role . '" '.$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 category,day_conditions,day_count,day_start,day_end 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();
if (!$month_day_result) {
echo json_encode(array('success' => false,'status' => "Monthday",'message' => "Month days Not Set"));
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 = 1 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');
if(!is_array($category)){
$category = [$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");
$prime_upd_query .= '';
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" => $roles, "Search Month" =>$search_month];
$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 . '"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Fandf 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');
if(!is_array($category)){
$category = [$category];
}
$roles = implode(",", $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" => $roles, "Search Month" =>$process_month];
$payroll_exist_qry = 'select count(*) as exist_count from cw_transactions where cw_transactions.trans_status =1 and cw_transactions.termination_status = 1 and role in ('.$roles .') 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);
}
// 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 === "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 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();
//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");
/* }else{
$day_end = $day_count;
}*/
$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,"Fandf 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();
$monthly_input_ctl_name = "monthly_input";
$data['module_id'] = $monthly_input_ctl_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 = "' . $monthly_input_ctl_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;
$company_info = $this->company_info();
$data['allow_left_emp'] = $company_info[0]->allow_left_emp;
$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 = (int)$this->input->post('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);
}
//IMPORT DATA FROM FILE PATH
// public function do_monthly_excel_import($import_id){
// $this->save_info();
// $filename = dirname(__FILE__) . "/php_excel/PHPExcel/IOFactory.php";
// include($filename);
// if ($import_id < 0) {
// return json_encode(array(
// 'success' => false,
// 'message' => "Invalid file upload"
// ));
// }
// //EDIT START
// $cat_qry = 'select * 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');
// $excel_path_qry = 'select * from cw_month_import where import_id = "' . $import_id . '"';
// $excel_path_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_path_qry')");
// $excel_path_result = $excel_path_info->result();
// $excel_path_info->next_result();
// if (!$excel_path_result) {
// return json_encode(array(
// 'success' => false,
// 'message' => "Invalid file upload"
// ));
// } else {
// $excel_file_path = $excel_path_result[0]->excel_file_path;
// $module_id = $excel_path_result[0]->module_id;
// $excel_format = $excel_path_result[0]->excel_format;
// $excel_sheet_name = (int) $excel_path_result[0]->excel_sheet_name;
// $excel_row_start = (int) $excel_path_result[0]->excel_start_row;
// $excel_row_end = (int) $excel_path_result[0]->excel_end_row;
// $process_month = $excel_path_result[0]->process_month;
// $category_id = $excel_path_result[0]->category;
// //Add Additional Column
// $add_input = "";
// $mi_process_type = "";
// $add_value = "";
// $upd_value = "";
// if($this->company_info[0]->mi_process_type !== 'role' && $this->company_info[0]->mi_process_type !== '0'){
// $mi_process_type = $this->company_info[0]->mi_process_type;
// $add_input = ','.$this->company_info[0]->mi_process_type;
// //$add_value = '".$add_value."';
// }
// //check payroll count
// $payroll_exist_qry = 'select employee_code from cw_transactions where trans_status = 1 and transactions_month ="' . $process_month . '" and role = "' . $category_id . '"';
// $payroll_exist_info = $this->db->query("CALL sp_a_run ('RUN','$payroll_exist_qry')");
// $payroll_exist_result = $payroll_exist_info->result_array();
// $payroll_exist_info->next_result();
// $pay_codes = array_column($payroll_exist_result,'employee_code');
// //employee check in employee master
// $emp_qry = 'select employee_code,date_of_joining,role,emp_name,prime_employees_id,resignation_date,termination_status '.$add_input.' from cw_employees where trans_status = 1 and role = "'.$category_id.'"';
// $emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
// $emp_data_result = $emp_data->result();
// $emp_data->next_result();
// $emp_code_result = array_map(function($v)
// {
// $return_array['employee_data'] = $v;
// $return_array['employee_code'] = $v->employee_code;
// return $return_array;
// }, $emp_data_result);
// $emp_code_data = array_column($emp_code_result, 'employee_data', 'employee_code');
// $lock_month_qry = 'select * from cw_monthly_input_lock where lock_month = "' . $process_month . '" and status = 1 and trans_status = 1';
// $lock_month_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_month_qry')");
// $lock_month_result = $lock_month_data->result();
// $lock_month_data->next_result();
// $lock_num_rows = $lock_month_data->num_rows();
// if ((int) $lock_num_rows > 0) {
// return json_encode(array(
// 'success' => false,
// 'message' => "Monthly input is locked this month, please unlocked to upload the files"
// ));
// exit(0);
// }
// $format_qry = 'select * from cw_util_excel_format where prime_excel_format_id = "' . $excel_format . '" and cw_util_excel_format.trans_status = 1';
// $format_info = $this->db->query("CALL sp_a_run ('SELECT','$format_qry')");
// $format_rslt = $format_info->result();
// $format_info->next_result();
// if (!$format_rslt) {
// return json_encode(array(
// 'success' => false,
// 'message' => "Please add excel format before import"
// ));
// }else{
// $exist_column_name = explode(",", $format_rslt[0]->exist_column_name);
// $excel_format_qry = 'select * from cw_util_excel_format_line inner join cw_form_setting on label_name = excel_line_column_name where excel_line_module_id = "' . $module_id . '" and prime_excel_format_id = "' . $excel_format . '" and cw_util_excel_format_line.trans_status = 1 group by excel_line_column_name';
// $excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
// $excel_format_result = $excel_format->result();
// $excel_format->next_result();
// $excel_columns = array_column($excel_format_result, 'excel_line_column_name');
// $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,2)';
// $lock_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_query')");
// $lock_result = $lock_data->result();
// $lock_data->next_result();
// $previous_columns = explode(",",$lock_result[0]->previous_column);
// $result_arr = array_intersect($excel_columns,$previous_columns);
// $error_string = implode(",", $result_arr);
// if(!empty($result_arr)){
// return json_encode(array(
// 'success' => false,
// 'message' => " Columns $error_string are not Allowed to import.. These are Carry Forward Inputs.."
// ));
// exit(0);
// }
// if (!$excel_format_result) {
// return json_encode(array(
// 'success' => false,
// 'message' => "Please map excel cell column before import"
// ));
// } else {
// try {
// $excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($excel_file_path);
// }
// catch (Exception $e) {
// die('Error loading file "' . pathinfo($excel_file_path, PATHINFO_BASENAME) . '": ' . $e->getMessage());
// return json_encode(array(
// 'success' => false,
// 'message' => "Invalid file or path"
// ));
// }
// $sheet = $excel_obj->getSheet($excel_sheet_name);
// if ($excel_row_end) {
// $total_rows = $excel_row_end;
// } else {
// $total_rows = $sheet->getHighestRow();
// }
// $highest_column = $sheet->getHighestColumn();
// $error_info = array();
// for ($row = $excel_row_start; $row <= $total_rows; $row++) {
// $exist_val = "";
// $prime_column_val = "";
// $prime_cell_val = "";
// $status_info = array();
// $status_info["Excel Row"] = $row;
// $prime_upd_query = "";
// foreach ($excel_format_result as $excel_info) {
// $column_name = $excel_info->excel_line_column_name;
// $column_value = $excel_info->excel_line_value;
// $field_type = (int) $excel_info->field_type;
// $get_cell_value = trim($sheet->getCell("$column_value$row")->getCalculatedValue());
// if ($column_name === "role") {
// if (!in_array($get_cell_value, $cat_data)) {
// $error_info[$row] = "Category Not Exist in DB";
// }
// } else if ($column_name === "employee_code") {
// $employee_code = $get_cell_value;
// //Get Salary Start and End Date;
// $emp_data = $emp_code_data[$get_cell_value];
// $emp_doj = $emp_data->date_of_joining;
// $category = $emp_data->role;
// $resign_date = $emp_data->resignation_date;
// $emp_status = (int)$emp_data->termination_status;
// $salary_dates = $this->Process_payroll_model->get_salary_date($category);
// $day_start = $salary_dates['day_start'];
// $day_end = $salary_dates['day_end'];
// $process_date = date("Y-m-d", strtotime($day_end . "-" . $process_month));
// $sal_start_date = date("Y-m-d", strtotime($day_start . "-" . $process_month));
// if (!array_key_exists($get_cell_value, $emp_code_data)) {
// $error_info[$row] = "Employee Code Not Exist in DB";
// }else
// if(strtotime($emp_doj) > strtotime($process_date)) {
// $error_info[$row] = "Date of Joining Greater than Process date";
// }else
// if(in_array($employee_code, $pay_codes)){
// $error_info[$row] = "Payroll Already Exist for this Employee..";
// }else
// if((strtotime($resign_date) < strtotime($sal_start_date)) && $emp_status === 1){
// $error_info[$row] = "Already Resigned Employee..";
// }
// }
// }
// }
// $can_process_count = count($error_info);
// if ((int) $can_process_count > 0) {
// return json_encode(array(
// 'success' => true,
// 'message' => "File Has Error",
// 'error_info' => $error_info
// ));
// } else {
// $status_array = array();
// for ($row = $excel_row_start; $row <= $total_rows; $row++) {
// $exist_val = "";
// $prime_column_val = "";
// $prime_cell_val = "";
// $status_info = array();
// $status_info["Excel Row"] = $row;
// $prime_upd_query = "";
// foreach ($excel_format_result as $excel_info) {
// $column_name = $excel_info->excel_line_column_name;
// $column_value = $excel_info->excel_line_value;
// //$get_cell_value = trim($sheet->getCell("$column_value$row")->getValue());
// $get_cell_value = trim($sheet->getCell("$column_value$row")->getCalculatedValue());
// if ($column_name === "role"){
// $cat_id = array_search("$get_cell_value", $cat_data);
// $get_cell_value = $cat_id;
// }
// if ($column_name === "employee_code"){
// $employee_code = $get_cell_value;
// $emp_data = $emp_code_data[$get_cell_value];
// $doj = $emp_data->date_of_joining;
// $emp_id = $emp_data->prime_employees_id;
// $cat = $emp_data->role;
// $emp_name = $emp_data->emp_name;
// $emp_cell_val = $emp_id;
// if($mi_process_type){
// $add_value = ",".$emp_data->$mi_process_type;
// $upd_value = $add_input.' = "'.$emp_data->$mi_process_type.'"';
// }
// }
// $prime_column_val .= $column_name . ",";
// $prime_cell_val .= '"' . $get_cell_value . '",';
// if (in_array($column_name, $exist_column_name)) {
// $exist_val .= $column_name . ' = "' . $get_cell_value . '" and ';
// }
// $update_column_val = $column_name;
// $update_cell_val = '"' . $get_cell_value . '",';
// $prime_upd_query .= $update_column_val . "=" . $update_cell_val;
// }
// $exist_val = rtrim($exist_val, " and ");
// $exist_query = 'select count(*) exist_count,trans_status,prime_monthly_input_id from cw_monthly_input where trans_status = 1 and ' . $exist_val . ' and process_month ="' . $process_month . '"';
// $exist_info = $this->db->query("CALL sp_a_run ('RUN','$exist_query')");
// $exist_result = $exist_info->result();
// $exist_info->next_result();
// $exist_count = $exist_result[0]->exist_count;
// $created_on = date("Y-m-d h:i:s");
// if ((int)$exist_count === 0){
// $prime_column_val .= "employees_id,emp_name,role,date_of_joining,process_month,trans_created_by,trans_created_date,fandf,input_status $add_input";
// $prime_cell_val .= '"' . $emp_cell_val . '","' . $emp_name . '","' . $category_id . '","' . $doj . '","' . $process_month . '","' . $this->logged_id . '",' . '"' . $created_on . '","1","1"'.$add_value;
// $prime_column_val = rtrim($prime_column_val, ",");
// $prime_cell_val = rtrim($prime_cell_val, ",");
// $prime_query = "insert into cw_monthly_input ($prime_column_val) VALUES ($prime_cell_val)";
// $insert_info = $this->db->query("CALL sp_a_run ('RUN','$prime_query')");
// $status_info['Status'] = "Inserted to DB";
// }else
// if((int) $exist_count > 0) {
// $trans_status = (int)$exist_result[0]->trans_status;
// $upd_prime_id = (int)$exist_result[0]->prime_monthly_input_id;
// $prime_upd_query .= 'trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '"';
// if($trans_status === 1){
// $upd_query = 'UPDATE cw_monthly_input SET ' . $prime_upd_query . ',input_status = 1,fandf=1 '.$upd_value.' WHERE prime_monthly_input_id = "' . $upd_prime_id . '" and role = "'.$category_id.'"';
// $this->db->query("CALL sp_a_run ('RUN','$upd_query')");
// $status_info['status'] = "Updated to DB";
// }else{
// $status_info['status'] = "Already Exist in DB";
// }
// }
// $status_array[] = $status_info;
// }
// }
// }
// $table_info = $this->get_excel_import_ui($status_array);
// return json_encode(array(
// 'success' => true,
// 'message' => "Successfully file imported",
// 'table_info' => $table_info
// ));
// }
// }
// }
//NEHA EDIT START 06APR2020
// public function excel($module_id, $excel_format){
// $excel_format_qry = 'select excel_name,view_name,excel_line_column_name,excel_line_value from cw_util_excel_format_line inner join cw_form_setting on cw_form_setting.label_name = excel_line_column_name inner join cw_util_excel_format on cw_util_excel_format.prime_excel_format_id = cw_util_excel_format_line.prime_excel_format_id where excel_line_module_id = "'.$module_id.'" and cw_util_excel_format_line.prime_excel_format_id ="'.$excel_format.'" and cw_util_excel_format_line.trans_status = 1 and cw_util_excel_format.trans_status = 1 GROUP BY cw_form_setting.label_name';
// $excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
// $excel_result = $excel_format->result();
// $excel_format->next_result();
// $excel_name = str_replace(' ', '_', $excel_result[0]->excel_name);
// require_once APPPATH . "/third_party/PHPExcel.php";
// $obj = new Spreadsheet();
// //Set the first row as the header row
// foreach ($excel_result as $excel) {
// $excel_line_column_name = $excel->view_name;
// $excel_line_value = $excel->excel_line_value;
// $obj->getActiveSheet()->setCellValue($excel_line_value . "1", $excel_line_column_name);
// }
// ob_end_clean();
// // Rename worksheet name
// $filename= $excel_name.".xls"; //save our workbook as this file name
// header('Content-Type: application/vnd.ms-excel'); //mime type
// header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name
// header('Cache-Control: max-age=0'); //no cache
// ob_end_clean();
// $objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($obj, 'Excel5');
// //force user to download the Excel file without writing it to server's HD
// $objWriter->save('php://output');
// echo json_encode(array(
// 'success' => TRUE,
// 'output' => $excelOutput
// ));
// }
//Sheet Name display in import page
// public function sheet_name()
// {
// $file_path = $this->input->post('file_path');
// $filename = dirname(__FILE__) . "/php_excel/PHPExcel/IOFactory.php";
// include($filename);
// $excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($file_path);
// $sheet_count = $excel_obj->getSheetCount();
// $sheet_name = array();
// for ($i = 0; $i < $sheet_count; $i++) {
// $sheet = $excel_obj->getSheet($i);
// $sheet_name[] = $sheet->getTitle();
// }
// echo json_encode(array(
// 'sheet_name' => $sheet_name
// ));
// }
//CUSTOM DELETE IS UPDATED
public function delete_month_input(){
//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);
}
$month_ids = implode(",", $this->input->post('delete_ids'));
$search_month = $this->input->post('search_month');
$category = $this->input->post('category');
if(!is_array($category)){
$category = [$category];
}
$roles = implode(",", $category ?? []);
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" => $roles, "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
$payroll_exist_qry = 'select count(*) payroll_count from cw_transactions where trans_status = 1 and transactions_month ="' . $search_month . '" and termination_status = 1 and role in (' . $roles . ') 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,"Fandf 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 = $this->input->post('category');
if(!is_array($category)){
$category = [$category];
}
$roles = implode(",", $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 = 1 and role in ('.$roles.')';
$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 termination_status = 1 and role in ('.$roles.')';
$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($roles,$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 in ('.$roles.') 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 in ('.$roles.') 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;
}
public function check_import_month(){
$process_month = $this->input->post("process_month");
$category = $this->input->post('category');
if(!is_array($category)){
$category = [$category];
}
$roles = implode(",", $category ?? []);
$payroll_exist_qry = 'select count(*) as exist_count from cw_transactions where cw_transactions.trans_status =1 and cw_transactions.termination_status = 1 and role in ('.$roles.') and transactions_month = "'.$process_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" => TRUE,'message' => 'Ok Proceed'));
}else{
echo json_encode(array("success" => FALSE,'message' => 'Payroll Already Exist This Month'));
}
}
}
?>