File: //home/cafsindia/hrms_allyindian_com/application_bk/controllers/Increment.php
<?php
/**********************************************************
Filename: Increment
Description: Increment.
Author: Sathish
Created on: 06 June 2019
Reviewed by:
Reviewed on:
Approved by:
Approved on:
-------------------------------------------------------
Modification Details
Changed by:
Change Info:
-------------------------------------------------------
***********************************************************/
if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
require('./application/libraries/PHPSpreadsheet/autoload.php');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Increment extends Action_controller{
public function __construct(){
parent::__construct('increment');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$this->load->model('Increment_calculation_model');
$this->load->model('Process_payroll_model');
$this->load->model('Hr_methods_model');
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$this->column_mapping();
$valid_array = $this->page_validation();
if($valid_array){
$data['valid_array'] = $this->validation_ui($valid_array);
}
//GET Monthly Input Columns
$data['table_headers'] = '';
$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 = "";
if($role_result){
$pick_key = array_column($role_result, "prime_category_id");
$pick_val = array_column($role_result, "category_name");
$category_list = array_combine( $pick_key, $pick_val);
}
if($category_list){
$category_list = array("" => "---- Select Category ----") + $category_list;
}
$data['category_list'] = $category_list;
//COMPANY INFORMATION
$company_info = $this->company_info();
$effective_month = $company_info[0]->max_effective_months;
if($effective_month){
$effective_month = '-'.$effective_month;
$start_date = date('Y-m-d', strtotime("$effective_month months"));
}
$data['start_date'] = $start_date;
$data['encKey'] = $this->generateKey();
$this->load->view('increment/manage',$data);
}
//Check Page Validation
public function page_validation(){
//Check arrear column mapped or not
$valid_array = array();
$check_arr_qry = 'SELECT COUNT(prime_form_id) as rslt_count FROM cw_form_setting LEFT OUTER JOIN cw_arrear_column_mapping ON cw_arrear_column_mapping.transaction_column=cw_form_setting.label_name WHERE increment_check=1 AND transaction_column IS NULL';
$check_arr_data = $this->db->query("CALL sp_a_run ('SELECT','$check_arr_qry')");
$check_arr_result = $check_arr_data->result();
$check_arr_data->next_result();
$arr_count = $check_arr_result[0]->rslt_count;
if((int)$arr_count > 0){
$valid_array['Arrear Column not Mapped'] = "Check All the Increment Column mapped with Arrear Columns in Arrear Column Mapping";
}
//,'earned_basic'=>'Earned Basic','earned_gross'=>'Earned Gross','arrear_gross'=>'Arrear Gross','arrear_pf_gross'=>'Arrear PF Gross'
$error_data = array('paid_days'=>'Paid Days','month_days'=>'Month Days','fixed_basic'=>'Fixed Basic');
//$mapping_data = $this->Hr_methods_model->get_mapped_db_column(12);
$result = array_diff_key($error_data, $this->get_map_arr);
if($result){
$result = implode(",",$result);
$valid_array['Function Not Mapped'] = "Function Column Mapping is not mapped - $result";
}
return $valid_array;
}
public function validation_ui($valid_array){
$tr_line = "";
foreach($valid_array as $type => $msg){
$tr_line .= "<tr $color><td>$type</td><td>$msg</td></tr>";
}
$table_info = "<div style='padding: 50px;'><table class='table table-bordered' style='text-align:center;'>
<thead>
<tr>
<th>Type</th>
<th>Validation Message</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table></div>";
return $table_info;
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
public function search_increment_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);
}
$effective_date = date("Y-m-d",strtotime($this->input->post('effective_date')));
$apply_month = $this->input->post('apply_month');
$employee_code = $this->input->post('employee_code');
$category = $this->input->post('category');
$before_days = $this->input->post('before');
$after_day = $this->input->post('after');
$category = $this->input->post('category');
$process_by = $this->input->post('process_by');
$created_on = date("Y-m-d H:i:s");
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" => $category , "Employee code"=>$employee_code, "Apply month" =>$apply_month];
if($category){
$inc_column_qry = 'select GROUP_CONCAT(label_name) as label_names from cw_form_setting where prime_module_id = "employees" and increment_check = 1 and trans_status = 1';
$inc_column_data = $this->db->query("CALL sp_a_run ('SELECT','$inc_column_qry')");
$inc_column_result = $inc_column_data->result();
$inc_column_data->next_result();
if(!$inc_column_result[0]->label_names){
echo json_encode(array('success' => false, 'message' => "Increment Columns Not Created"));
exit(0);
}
//GET Values
$value_qry = 'SELECT '.$inc_column_result[0]->label_names.' from cw_employees where employee_code = "'.$employee_code.'" and trans_status = 1';
$value_data = $this->db->query("CALL sp_a_run ('SELECT','$value_qry')");
$value_result = $value_data->result();
$value_data->next_result();
if($value_result){
foreach($value_result[0] as $column_name => $value){
$current_value = $value;
$new_value = $value;
$diff_value = $new_value - $current_value;
$exist_qry = 'SELECT count(*) as count from cw_increment where apply_on = "'.$apply_month.'" and employee_code = "'.$employee_code.'" and column_name = "'.$column_name.'" and trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$exist_data->next_result();
$exist_rows = $exist_result[0]->count;
if((int)$exist_rows === 0){
$insert_query = 'INSERT into cw_increment (apply_on,category,employee_code,effective_date,process_by,before_day,after_day,column_name,current_value,new_value,difference_value,trans_created_by,trans_created_date) values ("'.$apply_month.'","'.$category.'","'.$employee_code.'","'.$effective_date.'","'.$process_by.'","'.$before_days.'","'.$after_day.'","'.$column_name.'","'.$current_value.'","'.$new_value.'","'.$diff_value.'","'.$this->logged_id.'","'.date('Y-m-d H:i:s').'")';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Increment DATA ADD",'user');
$insert_info = $this->db->query($insert_query);
}else{
//,difference_value = "'. $diff_value .'" ,new_value = "'. $new_value .'"
$update_query = 'UPDATE cw_increment SET effective_date = "'. $effective_date .'",process_by = "'. $process_by .'",before_day = "'. $before_days .'",after_day = "'. $after_day .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'. $created_on .'" WHERE employee_code = "'. $employee_code .'" and apply_on = "'.$apply_month.'" and column_name = "'.$column_name.'"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Increment DATA UPDATE",'user');
$update_result = $this->db->query($update_query);
}
}
}else{
echo json_encode(array('success' => false, 'message' => "Increment Columns Not Mapped"));
exit(0);
}
// return $this->get_increment_data($category,$apply_month,$employee_code);
$table_data = $this->get_increment_data($category,$apply_month,$employee_code);
echo json_encode(array('success'=>true,'table_data'=>$table_data));
}else{
echo json_encode(array('total'=>0,'rows'=>0));
}
}
public function get_increment_data($category,$apply_month,$employee_code){
$this->column_mapping();
$fixed_basic_db = $this->get_map_arr['fixed_basic'];
$list_qry = 'SELECT prime_increment_id,column_name,current_value,difference_value,new_value,employee_code,apply_on,view_name,effective_date,after_day,process_by from cw_increment inner join cw_form_setting on cw_form_setting.label_name = cw_increment.column_name where apply_on = "'.$apply_month.'" and employee_code = "'.$employee_code.'" and cw_increment.trans_status = 1 group by label_name order by field_sort ASC';
$list_data = $this->db->query("CALL sp_a_run ('SELECT','$list_qry')");
$list_result = $list_data->result();
$list_data->next_result();
$num_rows = $list_data->num_rows();
$tr_line = "";
$arrear_gross = 0;
$arrear_basic = 0;
$total = 0; // new changes work by andrews sum of total current and new value
$new_val = 0;
foreach ($list_result as $table_info){
$diff_value = $table_info->difference_value;
$new_value = $table_info->new_value;
$effec_date = date("d-m-Y",strtotime($table_info->effective_date));
$arrear_gross = $arrear_gross + $new_value;
$current_value = $table_info->current_value;
if($table_info->column_name === $fixed_basic_db){
$arrear_basic = $new_value;
}
if(!$current_value){
$total = "0.00";
}else{
$total = $current_value + $total;
}
if(!$new_value){
$new_val = "0.00";
}else{
$new_val = $new_val + $new_value;
}
$hid_column = "<input type='hidden' name = 'column_name[]' id='column_name' value='$table_info->column_name'/>";
$diff_hid = "<input type='hidden' name = 'diff_value[]' id='diff_value' value='$diff_value'/>";
$current_hid = "<input type='hidden' name = 'current_value[]' id='current_value' value='$table_info->current_value'/>";
$check_has_formula = $this->check_has_formula($table_info->column_name);
if((int)$check_has_formula === 0){
$readonly = "";
}else{
$readonly = "readonly";
}
$tr_line.= "<tr>$current_hid $hid_column $diff_hid<td>$table_info->employee_code</td><td>$effec_date</td><td>$table_info->apply_on</td><td>$table_info->view_name</td><td>$table_info->current_value</td><td style='width: 15%;'><input type='text' class='form-control input-sm increment_cal number' name = 'new_value[]' id='$table_info->column_name' onchange=update_table($category,$table_info->prime_increment_id,'$table_info->current_value','$table_info->employee_code','$table_info->apply_on','$table_info->column_name','$table_info->effective_date','$table_info->after_day') value='$table_info->new_value' $readonly></td><td>$diff_value</td></tr>";
$btn = "<button class='btn btn-primary btn-xs' style='float:left;' id='save_increment' onclick=save_increment($category,$table_info->prime_increment_id,'$table_info->employee_code','$table_info->apply_on','$table_info->effective_date','$table_info->after_day','$arrear_basic','$arrear_gross',$table_info->process_by) > <i class='fa fa-floppy-o' aria-hidden='true'></i> Save Increment </button>";
}
$table_data = "<div class='row' style='padding: 10px;'><div class='col-md-12' style='float:right;'><div class='col-md-10'></div><div class='col-md-2'>$btn</div></div>
<table id='inc_table' class='table table hover' style='width:100% !important;'>
<thead>
<th>Employee Code</th>
<th>Effective Date</th>
<th>Apply Month</th>
<th>Column Name</th>
<th> Current Value</th>
<th>New Value</th>
<th>Differences</th>
</thead>
<tbody>$tr_line</tbody>
<tr style='font-weight: bold; color: blue;'>
<th>Total</th>
<th></th>
<th></th>
<th></th>
<th>$total</th>
<th>$new_val</th>
<th></th>
</tr>
</table></div>";
return $table_data;
}
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);
}
$prime_id = $this->input->post('prime_id');
$new_value = $this->input->post('new_value');
$diff_value = $this->input->post('diff_value');
$apply_month = $this->input->post('apply_month');
$employee_code = $this->input->post('employee_code');
$category = $this->input->post('category');
$created_on = date("Y-m-d H:i:s");
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" => $category , "Employee code"=>$employee_code, "Apply month" =>$apply_month];
// PAYROLL PROCESS CHECK [MS 30-08-2024]
$payroll_query = 'SELECT max(date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y") , "%Y-%m")) as transactions_month FROM cw_transactions WHERE employee_code = "'.$employee_code.'" and trans_status =1 ORDER BY `prime_transactions_id` DESC';
$payroll_info = $this->db->query("CALL sp_a_run ('SELECT','$payroll_query')");
$payroll_rslt = $payroll_info->result();
$payroll_info->next_result();
$trans_last_month = $payroll_rslt[0]->transactions_month;
$check_apply_mon = date('Y-m',strtotime("01-$apply_month"));
if($check_apply_mon === $trans_last_month){// [MS 30-08-2024]
echo json_encode(array('success'=>false,'message'=>'Payroll Already Processed...'));
exit(0);
}
$upd_query = 'UPDATE cw_increment SET new_value = "'. $new_value .'",difference_value = "'. $diff_value .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'. $created_on .'" WHERE prime_increment_id = "'. $prime_id .'"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Increment DATA UPDATE",'user');
$upd_result = $this->db->query($upd_query);
if($upd_result){
$table_data = $this->get_increment_data($category,$apply_month,$employee_code);
echo json_encode(array('success'=>true,'table_data'=>$table_data));
}
}
//Calculate Arrear values and store database
public function save_increment(){
$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);
}
$trans_array=array();
$prime_id = $this->input->post('prime_id');
$new_array = $this->input->post('new_value');
$current_array = $this->input->post('current_value');
$diff_array = $this->input->post('diff_value');
$employee_code = $this->input->post('employee_code');
$apply_month = $this->input->post('apply_on');
$trans_column = $this->input->post('column_name');
$effective_date = $this->input->post('effective_date');
$after_day = $this->input->post('after_day');
$category = $this->input->post('category');
$process_by = $this->input->post('process_by');
$created_on = date("Y-m-d H:i:s");
$effective = $this->input->post('effective_date'); //used for insert
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" => $category , "Employee code"=>$employee_code, "Apply month" =>$apply_month];
//Employee Data
$emp_qry = 'SELECT metro FROM `cw_employees` WHERE employee_code ="'.$employee_code.'"';
$emp_qry_data = $this->db->query("CALL sp_a_run ('RUN','$emp_qry')");
$emp_qry_result = $emp_qry_data->result();
$emp_qry_data->next_result();
$metro = $emp_qry_result->metro;
$formula_exist_qry = 'SELECT count(*) as count FROM `cw_form_bind_input`
INNER JOIN `cw_form_condition_formula` ON cw_form_condition_formula.prime_cond_id = cw_form_bind_input.input_cond_id
WHERE input_cond_module_id ="increment" AND line_input_bind_col !="" AND cw_form_condition_formula.trans_status=1 and cond_order != 0';
$formula_exist_data = $this->db->query("CALL sp_a_run ('RUN','$formula_exist_qry')");
$formula_exist_result = $formula_exist_data->result();
$formula_exist_data->next_result();
$formula_exist = $formula_exist_result[0]->count;
if((int)$formula_exist > 0){
$trans_array = $this->input->post('arr_for');
$trans_array['metro'] = $metro;
$final_trans_array[$employee_code] = $trans_array;
$new_array = $this->Increment_calculation_model->increment_calculation($final_trans_array,$employee_code);
}
// Arrear Insert Data
$apply_date = date("d",strtotime($effective_date))."-".$apply_month;
$date = strtotime($apply_date);
$apply_date = date('Y-m-d', $date);
$arrear_value = 0;
$cumulate_arrear_value = 0;
$arrear_pf = 0;
$cumulate_arrear_pf = 0;
$this->column_mapping();
$paid_days_db = $this->get_map_arr['paid_days'];
$arrear_pf_db = $this->get_map_arr['arrear_pf'];
$arrear_pf_gross_db = $this->get_map_arr['arrear_pf_gross'];
$month_days_db = $this->get_map_arr['month_days'];
$fixed_basic_db = $this->get_map_arr['fixed_basic'];
//Arrear PF Gross Formula
$get_arrear_pf_gross_qry = 'select payroll_formula from cw_payroll_formula where formula_for = "'.$category.'" and out_column = "'.$arrear_pf_db.'" and trans_status =1';
$arrear_pf_gross_data = $this->db->query("CALL sp_a_run ('SELECT','$get_arrear_pf_gross_qry')");
$arrear_pf_gross_result = $arrear_pf_gross_data->result();
$arrear_pf_gross_data->next_result();
$arrear_pf_formula = $arrear_pf_gross_result[0]->payroll_formula;
if(!$arrear_pf_formula){
echo json_encode(array('success' => false, 'message' => "Arrear PF Formula Not Created.."));
exit(0);
}
//Get Transaction Columns
// $get_trans_colums = 'SELECT GROUP_CONCAT(label_name) as label_name FROM cw_form_setting where arrear_pf_check = 1';
// $trans_colums_data = $this->db->query("CALL sp_a_run ('SELECT','$get_trans_colums')");
// $trans_colums_result = $trans_colums_data->result();
// $trans_colums_data->next_result();
// $trans_columns = explode(",",$trans_colums_result = $trans_colums_result[0]->label_name);
// if($trans_colums_result){
// // if(!$arrear_pf_gross_db){
// // echo json_encode(array('success' => false, 'message' => "Arrear PF Columns Not Mapped"));
// // exit(0);
// // }
// }
//Get Last Month
$eff_date_format = date("Y-m-d",strtotime($effective_date));
$datestring = "$eff_date_format first day of last month";
$dt = date_create($datestring);
$prev_month = $dt->format('Y-m');
$eff_date = date("d",strtotime($effective_date));
$eff_month = date("m-Y",strtotime($effective_date));
$start_date = $effective_date; // for get months
$startingSal = FALSE;
$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){
$sal_start = $day_start;
//For Current month between days increment
$date = new DateTime("01-$apply_month 00:00:00");
$date->modify('-1 month');
$salary_start_date = $date->format("Y-m-$sal_start");
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$apply_month));
//for get months
if($sal_start === $eff_date){
$startingSal = TRUE;
$start = new DateTime($effective_date);
$start->modify('first day of next month');
}else
if($eff_date > $sal_start){
$start = new DateTime($effective_date);
$start->modify('first day of next month');
}else{
$start = new DateTime($effective_date);
$start->modify('first day of this month');
}
}else{
$sal_start = '01';
if($sal_start === $eff_date){
$startingSal= TRUE;
}
$prev_month = date("Y-m",strtotime($eff_date_format));
$date = new DateTime("01-$apply_month 00:00:00");
$salary_start_date = $date->format("Y-m-$sal_start");
if((int)$day_conditions === 2){
$salary_end_date = date("Y-m-t",strtotime("01-".$apply_month));
}else{
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$apply_month));
}
$start = new DateTime($effective_date); //for get months
$start->modify('first day of this month');
}
}
$diff_check = FALSE;
if($effective_date >= $salary_start_date && $effective_date < $salary_end_date){
$diff_check = TRUE;
}
if($start){
$start_date = $start->format("Y-m-d");
}
$months = $this->get_months($salary_end_date,$start_date);
$prev_date = $prev_month."-".$sal_start;
$array_cum = array();
$i = 0;
$arr = array();
$arrear_pf_data = array();
// LOP CREDIT DAYS WILL ADDED ON WITH PAID DAYS _ARN 23-09-2025
$lop_credit_qry = 'SELECT employee_code, process_month, SUM(credit_days) AS total_credit_days FROM cw_lop_credit_trans WHERE trans_status = 1 AND employee_code = "'.$employee_code.'" GROUP BY employee_code, process_month';
$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();
$employee_lop_credits = [];
foreach($lop_credit_result as $row){
$employee_lop_credits[$row->process_month] = (float)$row->total_credit_days;
}
//column and month wise insert and update
foreach ($trans_column as $column_name){
$cumulate_arrear_value = 0;
$cumulate_arrear_pf = 0;
foreach ($months as $dt){
$current_value = $current_array[$i];
if((int)$formula_exist > 0){
$new_value = $new_array[$column_name];
$diff_value = ($new_value) - ($current_value);
}else{
$new_value = $new_array[$i];
$diff_value = $diff_array[$i];
}
$month = $dt->format("m-Y");
// echo "BSK $month <br/>";
$arr_date = $eff_date."-".$month;
$transaction_qry = 'select '.$paid_days_db.' from cw_transactions where transactions_month = "'.$month.'" and employee_code = "'.$employee_code.'" and trans_status = 1';
$transaction_data = $this->db->query("CALL sp_a_run ('SELECT','$transaction_qry')");
$transaction_result = $transaction_data->result();
$transaction_data->next_result();
$paid_days = $transaction_result[0]->$paid_days_db;
$month_days = $this->Process_payroll_model->get_total_work_days($category,$month);
// CREDIT DAYS ADDED WITH IN PAID DAYS _ARN 23-09-2025
$credit_days = $employee_lop_credits[$month];
if($eff_date !== "01"){
if($eff_month === $month){
if((int)$process_by === 1){
$lop_days = $this->dateDiff($prev_date, $eff_date_format);
if($sal_start === $eff_date){
$lop_days = $after_day;
}else{
$lop_days = $after_day + $lop_days;
}
$paid_days = $month_days - $lop_days;
}else{
$paid_days = $after_day;
}
}else{
if($diff_check){
$diff_days = $this->dateDiff($prev_date, $effective);
$paid_days = $month_days - $diff_days;
}else{
$paid_days = $paid_days;
}
}
}else{
//if($eff_month === $month){
if(($eff_month === $month) && (!$startingSal)){
if((int)$process_by === 1){
$lop_days = $this->dateDiff($prev_date, $eff_date_format);
if($sal_start === $eff_date){
$lop_days = $after_day;
}else{
$lop_days = $after_day + $lop_days;
}
$paid_days = $month_days - $lop_days;
}else{
$paid_days = $after_day;
}
}else{
$paid_days = $paid_days;
}
}
if($startingSal){
$paid_days = $paid_days + $credit_days;
}
$arrear_column_qry = 'select arrear_column from cw_arrear_column_mapping where transaction_column = "'.$column_name.'" and trans_status = 1';
$arrear_column_data = $this->db->query("CALL sp_a_run ('SELECT','$arrear_column_qry')");
$arrear_column_result = $arrear_column_data->result();
$arrear_column_data->next_result();
$arrear_column = $arrear_column_result[0]->arrear_column;
$arrear_value = ((($diff_value * $paid_days) / $month_days));
if($column_name === "gmc_in_ctc" && $this->config->item("db_name") === "denave_hrms_db" && $category === 2){
$arrear_value = $diff_value;
}
//echo "BSK $arrear_column :: $diff_value * $paid_days :: $month_days :: $arrear_value :: $month <br/>";
$arrear_date = date("Y-m-d",strtotime($arr_date));
$cumulate_arrear_value = (($cumulate_arrear_value + $arrear_value));
$arr[$month][$arrear_column] = $arrear_value;
if($arrear_column){
$exist_qry = 'SELECT * from cw_arrears where increment_apply_month = "'.$apply_month.'" and employee_code = "'.$employee_code.'" and transaction_column = "'.$column_name.'" and arrear_month = "'.$month.'"';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$exist_data->next_result();
$exist_rows = $exist_data->num_rows();
if((int)$exist_rows === 0){ //,"'.$arrear_pf.'",arrear_pf
$insert_query = 'insert into cw_arrears(increment_apply_month,category,employee_code,effective_date,transaction_column,arrear_column,arrear_month,arrear_value,paid_days,new_value,old_value,arrear_date,trans_created_by,trans_created_date) value ("'.$apply_month.'","'.$category.'","'.$employee_code.'","'.$effective.'","'.$column_name.'","'.$arrear_column.'","'.$month.'","'.$arrear_value.'","'. $paid_days .'","'.$new_value.'","'.$current_value.'","'.$arrear_date.'","'.$this->logged_id.'","'.date('Y-m-d H:i:s').'")';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Arrears DATA ADD",'');
$this->db->query($insert_query);
}else{ //,arrear_pf = "'. $arrear_pf .'"
$upd_arrears_query = 'UPDATE cw_arrears SET paid_days = "'. $paid_days .'",new_value = "'. $new_value .'",arrear_value = "'. $arrear_value .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'. $created_on .'" WHERE employee_code = "'. $employee_code .'" and increment_apply_month = "'.$apply_month.'" and transaction_column = "'.$column_name.'" and arrear_date = "'.$arrear_date.'"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Arrears DATA UPDATE",'');
$this->db->query($upd_arrears_query);
}
if(empty($arrear_pf_data[$employee_code][$month])){
$info = $this->db->query("CALL itsp_arr_pf('$month','$employee_code')");
$result = $info->result();
$info->next_result();
$arrear_pf = $result[0]->arrear_pf;
$arrear_pf_data[$employee_code][$month] = $arrear_pf;
}else{
$arrear_pf = $arrear_pf_data[$employee_code][$month];
}
$cumulate_arrear_pf = $cumulate_arrear_pf + $arrear_pf;
}
}
if($arrear_column){
$exist_qry = 'SELECT * from cw_arrear_cumulative where increment_apply_month = "'.$apply_month.'" and employee_code = "'.$employee_code.'" and transaction_column = "'.$column_name.'"';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$exist_data->next_result();
$exist_rows = $exist_data->num_rows();
if((int)$exist_rows === 0){
$cumulate_query = 'insert into cw_arrear_cumulative(increment_apply_month,category,employee_code,effective_date,transaction_column,arrear_column,arrear_value,arrear_pf,payroll_done,trans_created_by,trans_created_date) value ("'.$apply_month.'","'.$category.'","'.$employee_code.'","'.$effective.'","'.$column_name.'","'.$arrear_column.'","'.$cumulate_arrear_value.'","'.$cumulate_arrear_pf.'","Y","'.$this->logged_id.'","'.date('Y-m-d H:i:s').'")';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Arrear Cumulative DATA ADD",'');
$cumulate_result = $this->db->query($cumulate_query);
}else{
$upd_cumulative_query = 'UPDATE cw_arrear_cumulative SET arrear_value = "'. $cumulate_arrear_value .'",arrear_pf = "'. $cumulate_arrear_pf .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'. $created_on .'" WHERE employee_code = "'. $employee_code .'" and increment_apply_month = "'.$apply_month.'" and transaction_column = "'.$column_name.'"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Arrear Cumulative DATA UPDATE",'');
$upd_cumulative_result = $this->db->query($upd_cumulative_query);
}
}
$i++;
}
echo json_encode(array('success' => true, 'message' => "Increment Data Successfully Saved"));
}
public function get_employee_list(){
$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');
$trans_last_month = $this->get_last_transaction($category);
$emp_query = 'SELECT employee_code,emp_name FROM cw_employees WHERE role = "'.$category.'" and trans_status =1 and termination_status = 0';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_query')");
$emp_result = $emp_info->result();
$emp_info->next_result();
$emp_list = "<option value='0'>---- Select Employee ----</option>";
foreach($emp_result as $result){
$id = $result->employee_code;
$name = ucwords($result->emp_name);
$emp_code = $result->employee_code;
$emp_list .= "<option value='$id'>$emp_code - $name</option>";
}
if($trans_last_month){
echo json_encode(array('success' => true, 'emp_list' => $emp_list,'start_month'=>$trans_last_month));
}else{
echo json_encode(array('success' => false,'start_month'=>$trans_last_month));
}
}
public function get_lop_days(){
$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');
$employee = $this->input->post('employee');
$process_by = $this->input->post('process_by');
$apply_month = $this->input->post('apply_month');
if((int)$process_by === 1){
$days_column = "lop_days";
}else{
$days_column = "paid_days";
}
$effective_date = date("Y-m-d",strtotime($this->input->post('effective_date')));
$eff_date = date("d",strtotime($this->input->post('effective_date')));
$salary_dates = $this->Process_payroll_model->get_salary_date($category);
$day_start = $salary_dates['day_start'];
$day_end = $salary_dates['day_end'];
$day_conditions = $salary_dates['day_conditions'];
if((int)$day_conditions === 3 && $eff_date >= $day_start){
$effective_month = date("m-Y", strtotime("+1 month", strtotime($this->input->post('effective_date'))));
}else{
$effective_month = date("m-Y",strtotime($this->input->post('effective_date')));
}
//Date Of Joining Check
$doj_check_qry = 'select count(*) as rslt_count from cw_employees where employee_code= "'.$employee.'" and trans_status=1 and date_of_joining <="'.$effective_date.'"';
$doj_check_data = $this->db->query("CALL sp_a_run ('SELECT','$doj_check_qry')");
$doj_check_result = $doj_check_data->result();
$doj_check_data->next_result();
$rslt_count = $doj_check_result[0]->rslt_count;
//Dynamic column list array
$get_column_qry = 'select db_column from cw_payroll_function_map where loc_name = "'.$days_column.'" and trans_status=1';
$get_column_data = $this->db->query("CALL sp_a_run ('SELECT','$get_column_qry')");
$get_column_result = $get_column_data->result();
$get_column_data->next_result();
$lop_days_db = $get_column_result[0]->db_column;
if(!$lop_days_db){
echo json_encode(array('success' => false, 'message' => "Lop Days / Paid Days Not Mapped in function column mapping in employee module"));
exit(0);
}
$lop_qry = 'SELECT '.$lop_days_db.' from cw_transactions where process_month = "'.$effective_month.'" and employee_code = "'.$employee.'" and trans_status = 1';
$lop_data = $this->db->query("CALL sp_a_run ('SELECT','$lop_qry')");
$lop_result = $lop_data->result();
$lop_data->next_result();
$total_days = $lop_result[0]->$lop_days_db;
if(empty($lop_result)){
$total_days = 0;
if((int)$process_by !== 1){
$total_days = $this->Process_payroll_model->get_total_work_days($category,$effective_month);
}
}
if((int)$rslt_count === 1 ){
echo json_encode(array('success' => true, 'lop_days' => $total_days));
}else{
echo json_encode(array('success' => false, 'message' => "Date of joining not greater than effective date "));
}
}
public function check_has_formula($column){
$input_query = 'SELECT * FROM cw_form_bind_input
INNER JOIN cw_form_condition_formula ON cw_form_condition_formula.prime_cond_id = cw_form_bind_input.input_cond_id
WHERE input_cond_module_id ="increment" AND cond_drop_down ="role" AND line_input_bind_to ="'.$column.'" AND cw_form_condition_formula.trans_status=1';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$num_rows = $input_data->num_rows();
return $num_rows;
}
public function get_employees_delete(){
$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');
$apply_month = $this->input->post('apply_month');
// $payroll_qry = 'SELECT * from cw_transactions where transactions_month = "'.$apply_month.'" and role = "'.$category.'" and trans_status = 1';
// $payroll_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_qry')");
// $payroll_result = $payroll_data->result();
// $payroll_data->next_result();
// $num_rows = $payroll_data->num_rows();
// if((int)$num_rows > 0){
// echo json_encode(array('success'=>false,'message'=>'Payroll Processed For this Month'));
// }else{
$employee_qry = 'SELECT DISTINCT(cw_increment.employee_code) as employee_code,emp_name from cw_increment inner join cw_employees on cw_employees.employee_code = cw_increment.employee_code where apply_on = "'.$apply_month.'" and category = "'.$category.'" and cw_increment.trans_status = 1';
$list_data = $this->db->query("CALL sp_a_run ('SELECT','$employee_qry')");
$list_result = $list_data->result();
$list_data->next_result();
$tr_line = "";
$arrear_gross = 0;
$arrear_basic = 0;
foreach ($list_result as $table_info){
$employee_code = $table_info->employee_code;
$emp_name = $table_info->emp_name;
$tr_line.= "<tr><td width='140'><input type='checkbox' name = 'employees[]' id='employees' value='$employee_code'></td><td>$table_info->employee_code</td><td>$table_info->emp_name</td></tr>";
$btn = "<button class='btn btn-xs btn-danger' disabled='true' id='delete_increment' onclick=delete_increment('$category','$apply_month') >Delete</button>";
}
$table_data = "<div class='row' style='padding: 20px;'><div class='form-group'>
$btn
</div><table id='inc_table' class='table table hover' style='width:100% !important;'>
<thead>
<th><input type='checkbox' id='select_all'> Select All</th>
<th>Employee Code</th>
<th>Employee Name</th>
</thead>
<tbody>$tr_line</tbody>
</table></div>";
echo json_encode(array('success'=>true,'table_data'=>$table_data));
// }
}
public function delete_increment(){
$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');
$apply_month = $this->input->post('apply_month');
$employees = $this->input->post('employees');
$emp_code = implode('","',$employees);
$emp_pay_sts = 0;
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Category" => $category , "Employee code"=>$emp_code, "Apply month" =>$apply_month];
foreach($employees as $employee){
// THAT EMPLOYEE ALREADY PAYROLL PROCESSED NOT ALLOW TO DELETE
$payroll_qry = 'SELECT * from cw_transactions where transactions_month = "'.$apply_month.'" and role = "'.$category.'" and employee_code = "'.$employee.'" and trans_status = 1';
$payroll_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_qry')");
$payroll_result = $payroll_data->result();
$payroll_data->next_result();
$num_rows = $payroll_data->num_rows();
if((int)$num_rows > 0){
$emp_pay_sts++;
continue;
}else{
$qry = 'SELECT column_name,current_value from cw_increment where apply_on = "'.$apply_month.'" and category = "'.$category.'" and employee_code = "'.$employee.'" and cw_increment.trans_status = 1';
$increment_data = $this->db->query("CALL sp_a_run ('SELECT','$qry')");
$increment_result = $increment_data->result();
$increment_data->next_result();
foreach ($increment_result as $key => $value){
$update_qry = 'UPDATE cw_employees SET '.$value->column_name.' ="'.$value->current_value.'" where employee_code = "'.$employee.'" and trans_status = 1';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Employees Table DATA UPDATE",'');
$update_result = $this->db->query($update_qry);
}
$delete_qry = 'DELETE FROM cw_increment where apply_on = "'.$apply_month.'" and category = "'.$category.'" and employee_code = "'.$employee.'" and trans_status = 1';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Increment DATA DELETE",'user');
$delete_result = $this->db->query($delete_qry);
if($delete_result){
$arr_delete_qry = 'DELETE FROM cw_arrears where increment_apply_month = "'.$apply_month.'" and employee_code = "'.$employee.'" ';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Arrear DATA DELETE",'');
$arr_delete_result = $this->db->query($arr_delete_qry);
if($arr_delete_result){
$arrc_delete_qry = 'DELETE FROM cw_arrear_cumulative where increment_apply_month = "'.$apply_month.'" and employee_code ="'.$employee.'"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Arrear Cumulative DATA DELETE",'');
$arrc_delete_result = $this->db->query($arrc_delete_qry);
}
}
}
}
$employee_qry = 'SELECT DISTINCT(cw_increment.employee_code) as employee_code,emp_name from cw_increment inner join cw_employees on cw_employees.employee_code = cw_increment.employee_code where apply_on = "'.$apply_month.'" and category = "'.$category.'" and cw_increment.trans_status = 1';
$list_data = $this->db->query("CALL sp_a_run ('SELECT','$employee_qry')");
$list_result = $list_data->result();
$list_data->next_result();
$rslt_count = count($list_result);
$tr_line = "";
$arrear_gross = 0;
$arrear_basic = 0;
foreach ($list_result as $table_info){
$employee_code = $table_info->employee_code;
$emp_name = $table_info->emp_name;
$tr_line.= "<tr><td width='140'><input type='checkbox' name = 'employees[]' id='employees' value='$employee_code'></td><td>$table_info->employee_code</td><td>$table_info->emp_name</td></tr>";
$btn = "<button class='btn btn-xs btn-danger' id='delete_increment' onclick=delete_increment('$category','$apply_month') >Delete</button>";
}
if($rslt_count > 0){
$tr_line = $tr_line;
}else{
$tr_line = "<tr><td width='140'></td><td></td<td>No Data Available</td><td></td></tr>";
}
$table_data = "<div class='row' style='padding: 20px;'><div class='form-group'>
$btn
</div><table id='inc_table' class='table table hover' style='width:100% !important;'>
<thead>
<th><input type='checkbox' id='select_all'> Select All</th>
<th>Employee Code</th>
<th>Employee Name</th>
</thead>
<tbody>$tr_line</tbody>
</table></div>";
if($arrc_delete_qry && $emp_pay_sts === 0){
echo json_encode(array('success'=>true,'message'=>'Successfully Deleted','table_data'=>$table_data));
}else{
echo json_encode(array('success'=>true,'message'=>'Successfully Deleted But Some Employees Already Payroll Processed','table_data'=>$table_data));
}
}
//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;
$data['excel_cell_value'] = $this->get_excel_value(100);
$temp_format_qry = 'select prime_inc_temp_setting_id,template_name from cw_inc_temp_setting where trans_status = 1 and module_id="increment"';
$temp_format = $this->db->query("CALL sp_a_run ('SELECT','$temp_format_qry')");
$temp_result = $temp_format->result();
$temp_format->next_result();
$temp_format_drop[""] = "---- Template Format ----";
foreach($temp_result as $template){
$temp_format_id = $template->prime_inc_temp_setting_id;
$template_name = $template->template_name;
$temp_format_drop[$temp_format_id] = $template_name;
}
$data['temp_format_drop'] = $temp_format_drop;
$this->load->view("$this->control_name/import",$data);
}
//Excel ABC Generator Function
public function get_excel_value($tot_cell){
$excel = array(''=>'--- Excel cell value ---');
if((int)$tot_cell > 0){
for($i=0;$i<=$tot_cell;$i++){
$letter = $this->getNameFromNumber($i);
$excel[$letter] = $letter;
}
}
return $excel;
}
public function getNameFromNumber($num) {
$numeric = $num % 26;
$letter = chr(65 + $numeric);
$num2 = intval($num / 26);
if ($num2 > 0) {
return $this->getNameFromNumber($num2 - 1) . $letter;
} else {
return $letter;
}
}
//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));
// }
//Get Columns Data
public function get_columns_data(){
$inc_column_qry = 'select prime_form_id,label_name,view_name from cw_form_setting where prime_module_id = "employees" and increment_check = 1 and trans_status = 1';
$inc_column_data = $this->db->query("CALL sp_a_run ('SELECT','$inc_column_qry')");
$inc_column_result = $inc_column_data->result();
$inc_column_data->next_result();
foreach($inc_column_result as $column){
$label_name = $column->label_name;
$view_name = $column->view_name;
$excel_cell_value = $this->get_excel_value(100);
$column_name = form_input(array( 'name' =>"excel_line_column_name[]",'class' => 'form-control input-sm','value' =>$label_name,'type'=>'Hidden'));
$excel_cell_input = form_dropdown(array('onchange = map_check(this); name' =>$label_name,'id'=>'excel_line_value[]','class' => 'form-control input-sm map_check'), $excel_cell_value,$excel_line_value);
$tr_line .= "<tr>
<td>$column_name $view_name</td>
<td>$excel_cell_input</td>
</tr>";
}
echo "<table class='table table-bordered table-stripted'>
<tr class='inline_head'>
<th>Label Name</th>
<th>Excel Column</th>
</tr>
$tr_line
</table>";
}
//Get all the months from the effective date.
public function get_months($apply_date,$effective_date){
$start = new DateTime($effective_date);
$end = new DateTime($apply_date);
$interval = DateInterval::createFromDateString('1 month');
$period = new DatePeriod($start, $interval, $end);
return $period;
}
//get date difference_value
public function dateDiff($date1, $date2){
$date1_ts = strtotime($date1);
$date2_ts = strtotime($date2);
$diff = $date2_ts - $date1_ts;
return round($diff / 86400);
}
//Check Payroll is processed
public function check_payroll_process(){
$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 = $this->input->post('apply_month');
$category = $this->input->post('category');
$employee = $this->input->post('employee');
$payroll_qry = 'SELECT * from cw_transactions where transactions_month = "'.$month.'" and role = "'.$category.'" and employee_code = "'.$employee.'" and trans_status = 1';
$payroll_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_qry')");
$payroll_result = $payroll_data->result();
$payroll_data->next_result();
$num_rows = $payroll_data->num_rows();
if((int)$num_rows >= 1){
echo json_encode(array('success' => true, 'message' => "Payroll Processed For this Month"));
}else{
$list_qry = 'SELECT effective_date,process_by,before_day,after_day from cw_increment inner join cw_form_setting on cw_form_setting.label_name = cw_increment.column_name where apply_on = "'.$month.'" and employee_code = "'.$employee.'" and cw_increment.trans_status = 1 group by label_name';
$list_data = $this->db->query("CALL sp_a_run ('SELECT','$list_qry')");
$list_result = $list_data->result();
$list_data->next_result();
$effective_date = $list_result[0]->effective_date;
if($effective_date){
$effective_date = date('d-m-Y',strtotime($effective_date));
}
$process_by = $list_result[0]->process_by;
$before_day = $list_result[0]->before_day;
$after_day = $list_result[0]->after_day;
$salary_details = $this->salary_start_end_info($category,$month);
$salary_end_date = $salary_details['salary_end_date'];
echo json_encode(array('success'=>false,'effective_date'=>$effective_date,'process_by'=>$process_by,'before_day'=>$before_day,'after_day'=>$after_day,'salary_end_date'=>$salary_end_date));
}
}
public function check_template_details(){
$category = $this->input->post('category');
$sel_format_qry = 'select prime_inc_temp_setting_id,template_name from cw_inc_temp_setting inner join cw_increment_template on cw_increment_template.temp_name=cw_inc_temp_setting.prime_inc_temp_setting_id where category = "'.$category.'" and cw_inc_temp_setting.trans_status = 1 and cw_increment_template.trans_status= 1 group by template_name';
$sel_format = $this->db->query("CALL sp_a_run ('SELECT','$sel_format_qry')");
$sel_result = $sel_format->result();
$sel_format->next_result();
echo json_encode(array('success' => true, 'sel_result' => $sel_result));
}
public function emp_last_transaction(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$employee_code = $this->input->post('employee_code');
$payroll_query = 'SELECT max(date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y") , "%Y-%m")) as transactions_month FROM cw_transactions WHERE employee_code = "'.$employee_code.'" and trans_status =1 ORDER BY `prime_transactions_id` DESC';
$payroll_info = $this->db->query("CALL sp_a_run ('SELECT','$payroll_query')");
$payroll_rslt = $payroll_info->result();
$payroll_info->next_result();
$trans_last_month = $payroll_rslt[0]->transactions_month;
if($trans_last_month){
$trans_last_month = $trans_last_month."-01";
$trans_last_month = date('m-Y', strtotime('+1 month', strtotime($trans_last_month)));
}
echo json_encode(array('success' => true,'start_month'=>$trans_last_month));
}
public function save_inc_import(){
$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('import_category');
$template_name = $this->input->post('template_name');
$apply_month = $this->input->post('imp_apply_month');
$process_by = $this->input->post('process_by');
$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');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
$import_query = 'insert into cw_inc_import (category,template_name,apply_month,excel_file_path,process_by,excel_sheet_name,excel_start_row,excel_end_row,trans_created_by,trans_created_date) value ("'.$category.'","'.$template_name.'","'.$apply_month.'","'.$excel_file_path.'","'.$process_by.'","'.$excel_sheet_name.'","'.$excel_start_row.'","'.$excel_end_row.'","'.$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_excel_inc_import($import_id);
}
//===================== EXCEL IMPORT TO INCREMENT ===============================
public function do_excel_inc_import($import_id){
$logged_id = $this->session->userdata('logged_id');
if($import_id < 0){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}
$excel_path_qry = 'select * from cw_inc_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{
$category = $excel_path_result[0]->category;
$excel_format = $excel_path_result[0]->template_name;
$apply_month = $excel_path_result[0]->apply_month;
$process_by = $excel_path_result[0]->process_by;
$excel_file_path = $excel_path_result[0]->excel_file_path;
$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;
$formula_exist_qry = 'SELECT count(*) as formula_count FROM `cw_form_bind_input` INNER JOIN `cw_form_condition_formula` ON cw_form_condition_formula.prime_cond_id = cw_form_bind_input.input_cond_id WHERE input_cond_module_id ="employees" AND cond_order != 0 AND line_input_bind_col !="" AND cw_form_condition_formula.trans_status=1';
$formula_exist_data = $this->db->query("CALL sp_a_run ('RUN','$formula_exist_qry')");
$formula_exist_result = $formula_exist_data->result();
$formula_exist_data->next_result();
$formula_exist = $formula_exist_result[0]->formula_count;
//Increment Columns
$inc_column_qry = 'select GROUP_CONCAT(label_name) as column_name from cw_form_setting where prime_module_id = "employees" and increment_check = 1 and trans_status = 1';
$inc_column_data = $this->db->query("CALL sp_a_run ('SELECT','$inc_column_qry')");
$inc_column_result = $inc_column_data->result();
$inc_column_data->next_result();
$inc_columns = $inc_column_result[0]->column_name;
$inc_count = explode(",",$inc_columns);
$inc_count = count($inc_count);
//arrear mapping count
$arrear_column_qry = 'select count(*) as arrear_count from cw_arrear_column_mapping where trans_status = 1';
$arrear_column_data = $this->db->query("CALL sp_a_run ('SELECT','$arrear_column_qry')");
$arrear_column_result = $arrear_column_data->result();
$arrear_column_data->next_result();
$arrear_count = $arrear_column_result[0]->arrear_count;
//Arrear PF Gross Formula
$get_arrear_pf_gross_qry = 'select payroll_formula from cw_payroll_formula where formula_for = "'.$category.'" and out_column = "'.$arrear_pf_gross_db.'" and trans_status =1';
$arrear_pf_gross_data = $this->db->query("CALL sp_a_run ('SELECT','$get_arrear_pf_gross_qry')");
$arrear_pf_gross_result = $arrear_pf_gross_data->result();
$arrear_pf_gross_data->next_result();
$arrear_pf_gross_formula = $arrear_pf_gross_result[0]->payroll_formula;
//Get Transaction Columns
$get_trans_colums = 'SELECT GROUP_CONCAT(label_name) as label_name FROM cw_form_setting where arrear_pf_check = 1';
$trans_colums_data = $this->db->query("CALL sp_a_run ('SELECT','$get_trans_colums')");
$trans_colums_result = $trans_colums_data->result();
$trans_colums_data->next_result();
$trans_columns = explode(",",$trans_colums_result = $trans_colums_result[0]->label_name);
//Get Salary 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 ="'.$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();
//ARREAR PF FORMULA CHECK VERICATION _ARN 02-02-2026
$this->column_mapping();
$arrear_pf_db = $this->get_map_arr['arrear_pf'];
$get_arrear_pf_gross_qry = 'select payroll_formula from cw_payroll_formula where formula_for = "'.$category.'" and out_column = "'.$arrear_pf_db.'" and trans_status =1';
$arrear_pf_gross_data = $this->db->query("CALL sp_a_run ('SELECT','$get_arrear_pf_gross_qry')");
$arrear_pf_gross_result = $arrear_pf_gross_data->result();
$arrear_pf_gross_data->next_result();
$arrear_pf_formula = $arrear_pf_gross_result[0]->payroll_formula;
if(!$arrear_pf_formula){
echo json_encode(array('success' => false, 'message' => "Arrear PF Formula Not Created.."));
exit(0);
}
// PAYROLL ALREADY PROCESSED VERFICATION _ARN 02-02-2026
$processed_qry = 'SELECT employee_code FROM cw_transactions WHERE transactions_month = "'.$apply_month.'" AND role = "'.$category.'" AND trans_status = 1';
$processed_data = $this->db->query("CALL sp_a_run ('SELECT','$processed_qry')");
$processed_result = $processed_data->result_array();
$processed_data->next_result();
$processed_arr = array_column($processed_result, 'employee_code');
// LAST TRANSACTION MONTH VERIFICATION _ARN 02-02-2026
$last_trans_qry = 'SELECT employee_code,MAX(date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y"), "%Y-%m")) AS transactions_month FROM cw_transactions WHERE trans_status = 1 GROUP BY employee_code';
$last_trans_data = $this->db->query("CALL sp_a_run ('SELECT','$last_trans_qry')");
$last_trans_result = $last_trans_data->result_array();
$last_trans_data->next_result();
$last_trans_arr = [];
foreach($last_trans_result as $row){
$last_trans_arr[$row['employee_code']] = $row['transactions_month'];
}
//Excel Query
$format_qry = 'select employee_code,effective_date,before_day,after_day,GROUP_CONCAT(column_name) as column_name, GROUP_CONCAT(column_map) as column_map from cw_increment_template where temp_name = "'.$excel_format.'" and category = "'.$category.'" and trans_status = 1';
$format_info = $this->db->query("CALL sp_a_run ('SELECT','$format_qry')");
$format_rslt = $format_info->result();
$format_info->next_result();
$employee_code_column = $format_rslt[0]->employee_code;
$effective_date_column = $format_rslt[0]->effective_date;
$before_day_column = $format_rslt[0]->before_day;
$after_day_column = $format_rslt[0]->after_day;
$column_name = explode(",",$format_rslt[0]->column_name);
$column_map = explode(",",$format_rslt[0]->column_map);
$column_value = array_combine($column_name,$column_map);
if(!$format_rslt){
return json_encode(array('success' => false, 'message' => "Please add excel format 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();
}
$emp_check_qry = 'select employee_code,date_of_joining,termination_status from cw_employees where trans_status= 1 and role = "'.$category.'"';
$emp_check_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_check_qry')");
$emp_check_result = $emp_check_data->result_array();
$emp_check_data->next_result();
$emp_result_arr = array_reduce($emp_check_result, function($result, $arr){
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
//CEHCK VALIDATIONS
$error_log = array();
for($row = $excel_row_start; $row <= $total_rows; $row++){
$employee_code = trim($sheet->getCell("$employee_code_column$row")->getCalculatedValue());
$date_cell_value = $sheet->getCell("$effective_date_column$row")->getCalculatedValue();
if(is_numeric($date_cell_value)){
$effective_date = date('Y-m-d',\PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($date_cell_value));
}else{
$effective_date = date('Y-m-d', strtotime($date_cell_value));
}
$before_days = trim($sheet->getCell("$before_day_column$row")->getCalculatedValue());
$after_days = trim($sheet->getCell("$after_day_column$row")->getCalculatedValue());
// WILL PAYROLL PROCESSED CHECK _ARN 02-02-2026
if(in_array($employee_code, $processed_arr)){
$error_log['error'][$employee_code] = "Payroll already processed for $apply_month";
}else
// WILL CHECK LAST TRANSACTION MONTH _ARN 02-02-2026
if(isset($last_trans_arr[$employee_code])){
$trans_last = $last_trans_arr[$employee_code];
$trans_last_dt = strtotime($trans_last . '-01');
$apply_month_dt = strtotime('01-' . $apply_month);
$next_allowed = strtotime('+1 month', $trans_last_dt);
if($apply_month_dt != $next_allowed){
$allowed_month = date('m-Y', $next_allowed);
$last_month = date('m-Y', $trans_last_dt);
$error_log['error'][$employee_code] ="Payroll already processed till $last_month . Next allowed month is $allowed_month";
}
}
if((int)$process_by === 2 && ((int)$before_days === 0 && (int)$after_days === 0)){
$error_log['error'][$employee_code] = "Please enter valid days...";
}
if(!empty($emp_result_arr)){
$date_of_joining = $emp_result_arr[$employee_code]['date_of_joining'];
$termination_status = $emp_result_arr[$employee_code]['termination_status'];
//Check Effective date format
$excel_cell_formate = $sheet->getCell($effective_date_column.$row)->getStyle()->getNumberFormat()->getFormatCode();
$cell_formate = str_replace("[$-14009]","",$excel_cell_formate);
$cell_formate = trim(strtoupper(str_replace(";@","",$cell_formate)));
$cell_formate = str_replace('\-', '-', $cell_formate);
if($cell_formate === "DD/MM/YYYY" || $cell_formate === "DD-MM-YYYY"){
if($cell_formate === "DD/MM/YYYY"){
$year_month_rslt = explode('/', $col_value);
}else
if($cell_formate === "DD-MM-YYYY"){
$year_month_rslt = explode('-', $col_value);
}
$date = $year_month_rslt[0];
$month = $year_month_rslt[1];
$year = $year_month_rslt[2];
$tot_days = cal_days_in_month(CAL_GREGORIAN,$month,$year);
if(((int)$date === 0) || ((int)$month === 0) || ((int)$year === 0)){
$err_column_array['error']["$row"] = $view_name;
$msg_line = "Please enter valid date... Please map The Effective Date Format Like (DD/MM/YYYY)";
$err_column_tabview['error']["$row"] = $view_name." ".$msg_line;
}else
if((int)$month > 12){
$err_column_array['error']["$row"] = $view_name;
$msg_line = "Invalid Month... Please map The Effective Date Format Like (DD/MM/YYYY)";
$err_column_tabview['error']["$row"] = $view_name." ".$msg_line;
}else
if((int)$date > (int)$tot_days){
$err_column_array['error']["$row"] = $view_name;
$msg_line = "Invalid date... Please map The Effective Date Format Like (DD/MM/YYYY)";
$err_column_tabview['error']["$row"] = $view_name." ".$msg_line;
}
}else{
$err_column_array['error']["$row"] = $view_name;
$msg_line = "Invalid Date Format... Please map The Effective Date Format Like (DD/MM/YYYY)";
$err_column_tabview['error']["$row"] = $view_name." ".$msg_line;
}
if((int)$termination_status === 1){
$error_log['error']["$row"] = "Already Resigned Employee or Employee code not Present in this Category..";
}else
if($effective_date < $date_of_joining){
$error_log['error']["$row"] = "Effective Date Must be greater than Date of Joining";
}else
if(!$emp_result_arr[$employee_code]){
$error_log['error']["$row"] = "Employee Code not Present in this Category..";
}
}else{
$error_log['error']["$row"] = "Employees not present in this Category..";
}
}
if(!empty($error_log)){
$inv_error_ui = $this->get_excel_error_ui($error_log);
echo json_encode(array('success' => FALSE, 'message' => "Excel Sheet has an error..","table_data"=>$inv_error_ui));
}else{
$success_log = array();
for($row = $excel_row_start; $row <= $total_rows; $row++){
$employee_code = trim($sheet->getCell("$employee_code_column$row")->getCalculatedValue());
$date_cell_value = $sheet->getCell("$effective_date_column$row")->getCalculatedValue();
if(is_numeric($date_cell_value)){$effective_date = date('Y-m-d',\PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($date_cell_value)
);
}else{
$effective_date = date('Y-m-d', strtotime($date_cell_value));
}
$before_days = trim($sheet->getCell("$before_day_column$row")->getCalculatedValue());
$after_days = trim($sheet->getCell("$after_day_column$row")->getCalculatedValue());
//Table Data
$table_data = array(
'apply_on' => $apply_month,
'category' => $category,
'employee_code' => $employee_code,
'effective_date' => $effective_date,
'before_day' => $before_days,
'process_by' => $process_by,
'after_day' => $after_days
);
$value_qry = 'SELECT '.$inc_columns.' from cw_employees where employee_code = "'.$employee_code.'" and trans_status = 1';
$value_data = $this->db->query("CALL sp_a_run ('SELECT','$value_qry')");
$value_result = $value_data->result();
$value_data->next_result();
$numrows = $value_data->num_rows();
$column_array = array();
$new_array = array();
$current_array = array();
$diff_array = array();
$trans_array = array();
$current_value = 0.00;
$new_value = 0.00;
$diff_value = 0.00;
$tbl_data = "";
if((int)$numrows !== 0){
foreach($value_result[0] as $colum_key => $current_value){
$colum_val = $column_value[$colum_key];
if($colum_val){
$new_value = trim($sheet->getCell("$colum_val$row")->getCalculatedValue());
}else{
$new_value = 0.00;
}
$diff_value = $new_value - $current_value;
$column_array[] = $colum_key;
$new_array[$colum_key] = $new_value;
$current_array[$colum_key] = $current_value;
$diff_array[$colum_key] = $diff_value;
if($new_value){
$trans_array[$employee_code][$colum_key] = $new_value;
}else{
$trans_array[$employee_code][$colum_key] = $current_value;
}
$table_data['column_name'] = $colum_key;
$table_data['current_value'] = $current_value;
$table_data['new_value'] = $new_value;
$table_data['difference_value'] = $diff_value;
$tbl_data = json_encode($table_data);
//SAVE INCREMENT
$insert_result = $this->db->query("CALL sp_increment ('SAVE_INCREMENT', '$tbl_data','$logged_id')");
$result = $insert_result->result();
$insert_result->next_result();
}
if((int)$formula_exist > 0){
$trans_array[$employee_code]['role'] = $category;
$new_array = $this->Increment_calculation_model->increment_calculation($trans_array,$employee_code);
$new_array = $new_array[$employee_code];
$unset_array = array_diff_key($new_array,$current_array);
foreach($unset_array as $unset => $val){
unset($new_array[$unset]);
}
$subtracted = array_map(function ($x, $y){
return $y-$x; } , $current_array, $new_array);
$diff_array = array_combine(array_keys($current_array), $subtracted);
}
if($result){
$rslt = $this->arrear_insert($trans_columns,$month_day_result,$category,$apply_month,$employee_code,$effective_date,$column_array,$arrear_column,$diff_array,$current_array,$new_array,$after_days,$process_by,$arrear_pf_gross_formula);
if($rslt){
$success_log['success'][$employee_code] = "Updated to DB";
}else{
$success_log['success'][$employee_code] = "Please try After Sometime";
}
}
}
}
if(!empty($success_log)){
$inv_success_ui = $this->get_excel_error_ui($success_log);
echo json_encode(array('success' => true, 'message' => "Increment Data Successfully Saved","table_data"=>$inv_success_ui));
}
}
}
}
}
//Update increment values
public function arrear_insert($trans_columns,$month_day_result,$category,$apply_month,$employee_code,$effective_date,$column_array,$arrear_column,$diff_array,$current_array,$new_array,$after_day,$process_by,$arrear_pf_gross_formula){
$logged_id = $this->session->userdata('logged_id');
$created_on = date("Y-m-d H:i:s");
// Arrear Insert Data
$apply_date = date("d",strtotime($effective_date))."-".$apply_month;
$date = strtotime($apply_date);
$apply_date = date('Y-m-d', $date);
$arrear_value = 0;
$cumulate_arrear_value = 0;
$arrear_pf = 0;
$cumulate_arrear_pf = 0;
//Get Last Month
$eff_date_format = date("Y-m-d",strtotime($effective_date));
$datestring = "$eff_date_format first day of last month";
$dt = date_create($datestring);
$prev_month = $dt->format('Y-m');
$eff_date = date("d",strtotime($effective_date));
$eff_month = date("m-Y",strtotime($effective_date));
$startingSal = FALSE;
//Dynamic column list array to get column name
$mapping_data = $this->Hr_methods_model->get_mapped_db_column(12);
$paid_days_db = $mapping_data['paid_days'];
$fixed_basic_db = $mapping_data['fixed_basic'];
$arrear_gross_db = $mapping_data['arrear_gross'];
$arrear_pf_gross_db = $mapping_data['arrear_pf_gross'];
if($month_day_result){
$day_conditions = $month_day_result[0]->day_conditions;
$day_start = $month_day_result[0]->day_start;
$day_end = $month_day_result[0]->day_end;
if((int)$day_conditions === 3){
$sal_start = $day_start;
//For Current month between days increment
$date = new DateTime("01-$apply_month 00:00:00");
$date->modify('-1 month');
$salary_start_date = $date->format("Y-m-$sal_start");
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$apply_month));
//for get months
if($sal_start === $eff_date){
$startingSal = TRUE;
$start = new DateTime($effective_date);
$start->modify('first day of next month');
}else
if($eff_date > $sal_start){
$start = new DateTime($effective_date);
$start->modify('first day of next month');
}else{
$start = new DateTime($effective_date);
$start->modify('first day of this month');
}
}else{
$sal_start = '01';
if($sal_start === $eff_date){
$startingSal= TRUE;
}
$prev_month = date("Y-m",strtotime($eff_date_format));
$date = new DateTime("01-$apply_month 00:00:00");
$salary_start_date = $date->format("Y-m-$sal_start");
if((int)$day_conditions === 2){
$salary_end_date = date("Y-m-t",strtotime("01-".$apply_month));
}else{
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$apply_month));
}
$start = new DateTime($effective_date); //for get months
$start->modify('first day of this month');
}
}
$diff_check = FALSE; //check weather middle increment for same apply month
if($effective_date >= $salary_start_date && $effective_date < $salary_end_date){
$diff_check = TRUE;
}
$start_date = $start->format("Y-m-d");
$months = $this->get_months($salary_end_date,$start_date);
$prev_date = $prev_month."-".$sal_start;
// LOP CREDIT DAYS WILL ADDED ON WITH PAID DAYS _ARN 23-09-2025
$lop_credit_qry = 'SELECT employee_code, process_month, SUM(credit_days) AS total_credit_days FROM cw_lop_credit_trans WHERE trans_status = 1 AND employee_code = "'.$employee_code.'" GROUP BY employee_code, process_month';
$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();
$employee_lop_credits = [];
foreach($lop_credit_result as $row){
$employee_lop_credits[$row->process_month] = (float)$row->total_credit_days;
}
// GET TRANSACTION DATA CODE AND MONTH WISE _ARN 16-09-2025 START
$transaction_bul_qry = 'SELECT employee_code, transactions_month, '.$paid_days_db.' AS paid_days FROM cw_transactions WHERE trans_status = 1 AND STR_TO_DATE(CONCAT("01-", transactions_month), "%d-%m-%Y") BETWEEN "'.$start_date.'" AND "'.$salary_end_date.'"';
$transaction_bul_data = $this->db->query("CALL sp_a_run('SELECT', '".$transaction_bul_qry."')");
$transaction_bul_result = $transaction_bul_data->result();
$transaction_bul_data->next_result();
$result_array = [];
foreach($transaction_bul_result as $row){
$emp_code = $row->employee_code;
$txn_month = $row->transactions_month;
$paid_days = $row->paid_days;
$result_array[$emp_code][$txn_month] = [
$paid_days_db => $paid_days
];
}
// GET TRANSACTION COLUMN AGAINST ARREAR COLUMN
$arrear_column_qry = "SELECT transaction_column, arrear_column FROM cw_arrear_column_mapping WHERE trans_status = 1";
$arrear_column_data = $this->db->query("CALL sp_a_run ('SELECT', '$arrear_column_qry')");
$arrear_column_result = $arrear_column_data->result();
$arrear_column_data->next_result();
$mapping = [];
if(!empty($arrear_column_result)){
foreach($arrear_column_result as $row){
$mapping[$row->transaction_column] = $row->arrear_column;
}
}
// END :16-09-2025
//Table Data
$table_data = array('apply_on'=> $apply_month,'employee_code'=> $employee_code,'effective_date'=> $effective_date);
$i = 0;
$arr = array();
$tbl_data = "";
$arrear_pf_data = array();
//column and month wise insert and update
foreach ($column_array as $column_name){
$cumulate_arrear_value = 0;
$cumulate_arrear_pf = 0;
$arrear_pf = 0;
$paid_days = 0;
foreach ($months as $dt){
$diff_value = $diff_array[$column_name];
$new_value = $new_array[$column_name];
$current_value = $current_array[$column_name];
$month = $dt->format("m-Y");
$arr_date = $eff_date."-".$month;
//Get Transaction Data from every month
// $transaction_qry = 'select '.$paid_days_db.' from cw_transactions where transactions_month = "'.$month.'" and employee_code = "'.$employee_code.'" and trans_status = 1';
// $transaction_data = $this->db->query("CALL sp_a_run ('SELECT','$transaction_qry')");
// $transaction_result = $transaction_data->result();
// $transaction_data->next_result();
// $paid_days = $transaction_result[0]->$paid_days_db;
// ARRAY WAY TO GET PAID DAYS ON DYNAMIC _ARN 16-09-2025
$paid_days = $result_array[$employee_code][$month][$paid_days_db];
$month_days = $this->Process_payroll_model->get_total_work_days($category,$month);
// CREDIT DAYS ADDED WITH IN PAID DAYS _ARN 23-09-2025
$credit_days = $employee_lop_credits[$month];
if($eff_date !== "01"){
if($eff_month === $month){
if((int)$process_by === 1){
$lop_days = $this->dateDiff($prev_date, $eff_date_format);
if($sal_start === $eff_date){
$lop_days = $after_day;
}else{
$lop_days = $after_day + $lop_days;
}
$paid_days = $month_days - $lop_days;
}else{
$paid_days = $after_day;
}
}else{
if($diff_check){
$diff_days = $this->dateDiff($prev_date, $effective_date);
$paid_days = $month_days - $diff_days;
}else{
$paid_days = $paid_days;
}
}
}else{
//if($eff_month === $month){
if(($eff_month === $month) && (!$startingSal)){
if((int)$process_by === 1){
$lop_days = $this->dateDiff($prev_date, $eff_date_format);
if($sal_start === $eff_date){
$lop_days = $after_day;
}else{
$lop_days = $after_day + $lop_days;
}
$paid_days = $month_days - $lop_days;
}else{
$paid_days = $after_day;
}
}else{
$paid_days = $paid_days;
}
}
if($startingSal){
$paid_days = $paid_days +$credit_days;
}
// $arrear_column_qry = 'select arrear_column from cw_arrear_column_mapping where transaction_column = "'.$column_name.'" and trans_status = 1';
// $arrear_column_data = $this->db->query("CALL sp_a_run ('SELECT','$arrear_column_qry')");
// $arrear_column_result = $arrear_column_data->result();
// $arrear_column_data->next_result();
// $arrear_column = $arrear_column_result[0]->arrear_column;
// ARRAY WAY TO GET ARREAR COLUMN _ARN 16-09-2025;
$arrear_column = $mapping[$column_name];
if($column_name === $fixed_basic_db){
$arrear_basic_db = $arrear_column;
}
if($arrear_column){
$arrear_value = (($diff_value * $paid_days) / $month_days);
if($column_name === "gmc_in_ctc" && $this->config->item("db_name") === "denave_hrms_db" && $category === 2){
$arrear_value = $diff_value;
}
$arrear_date = date("Y-m-d",strtotime($arr_date));
$cumulate_arrear_value = $cumulate_arrear_value + $arrear_value;
$arr[$month][$arrear_column] = $arrear_value;
$table_data['category'] = $category;
$table_data['column_name'] = $column_name;
$table_data['arrear_column'] = $arrear_column;
$table_data['arrear_month'] = $month;
$table_data['arrear_value'] = $arrear_value;
$table_data['paid_days'] = $paid_days;
$table_data['new_value'] = $new_value;
// $table_data['arrear_pf'] = $arrear_pf;
$table_data['current_value'] = $current_value;
$table_data['arrear_date'] = $arrear_date;
$tbl_data = json_encode($table_data);
//SAVE Increment
$arrear_qry = $this->db->query("CALL sp_increment ('SAVE_ARREARS', '$tbl_data','$logged_id')");
$arrear_result = $arrear_qry->result();
$arrear_qry->next_result();
// CONTROL THE EXCUTE MANY TIME ON INSIDE LOOP _ARN (16-092025)
if (empty($arrear_pf_data[$employee_code][$month])) {
$info = $this->db->query("CALL itsp_arr_pf('$month','$employee_code')");
$result = $info->result();
$info->next_result();
$arrear_pf = $result[0]->arrear_pf;
$arrear_pf_data[$employee_code][$month] = $arrear_pf;
}else{
$arrear_pf = $arrear_pf_data[$employee_code][$month];
}
$cumulate_arrear_pf = $cumulate_arrear_pf + $arrear_pf;
}
}
if($arrear_column){
$table_data['payroll_done'] = "Y";
$table_data['cumulate_arrear_value'] = $cumulate_arrear_value;
$table_data['cumulate_arrear_pf'] = $cumulate_arrear_pf;
$tbl_data = json_encode($table_data);
//SAVE Increment
$arrear_cumulate_qry = $this->db->query("CALL sp_increment ('SAVE_CUMULATIVE', '$tbl_data','$logged_id')");
$arrear_cumulate_result = $arrear_cumulate_qry->result();
$arrear_cumulate_qry->next_result();
}
$i++;
}
// $result = $this->calculate_arrear_pf($trans_columns,$category,$employee_code,$apply_month,$arrear_gross_db,$arrear_pf_gross_db,$arr,$arrear_basic_db,$arrear_pf_gross_formula);
// if($result){
return $result;
// }
}
//Arrear PF Calculation
public function calculate_arrear_pf($trans_columns,$category,$employee_code,$apply_month,$arrear_gross_db,$arrear_pf_gross_db,$arr,$arrear_basic_db,$arrear_pf_gross_formula){
$created_on = date("Y-m-d H:i:s");
//Arrear Gross Formula
$get_arrear_gross_qry = 'select payroll_formula from cw_payroll_formula where formula_for = "'.$category.'" and out_column = "'.$arrear_gross_db.'" and trans_status =1';
$arrear_gross_data = $this->db->query("CALL sp_a_run ('SELECT','$get_arrear_gross_qry')");
$arrear_gross_result = $arrear_gross_data->result();
$arrear_gross_data->next_result();
$arrear_gross_formula = $arrear_gross_result[0]->payroll_formula;
$i = 1; //for generate dynamic file name and function name
$cumulate_pf = 0;
$cumulate_arrear_gross = 0;
$arrear_gr = 0;
foreach ($arr as $arrear_month => $value){
$pf_array = array();
$code = "";
$pf_calculation = $arrear_pf_gross_formula;
$arrear_gross = $arrear_gross_formula;
if($trans_columns){
foreach ($trans_columns as $trans_column){
if($trans_column){
$transaction_qry = 'SELECT '.$trans_column.' from cw_transactions where transactions_month = "'.$arrear_month.'" and employee_code = "'.$employee_code.'" and trans_status = 1';
$transaction_data = $this->db->query("CALL sp_a_run ('SELECT','$transaction_qry')");
$transaction_result = $transaction_data->result();
$transaction_data->next_result();
$trans_value = $transaction_result[0]->$trans_column;
$pf_calculation = str_replace("@".$trans_column."@", $trans_value, $pf_calculation);
}
}
}else{
return false;
}
foreach ($value as $column => $arrear_value){
if($column === $arrear_basic_db){
$arrear_basic = $arrear_value;
}
$change_column = "@".$column."@";
if($column !== $arrear_gross_db){
$arrear_gr = $arrear_value;
}
$pf_calculation = str_replace($change_column, $arrear_value, $pf_calculation);
}
$oldmask = umask(0);
$rand = (rand(1,100));
$pf_calculation = str_replace("@".$arrear_gross_db."@",$arrear_gross,$pf_calculation);
$filename = "calculation_file/pf_calculation_file_".$rand.".php";
$pf_calculation_func = "calculation_".$rand;
$code = "<?php function $pf_calculation_func(){ $pf_calculation } function $arrear_gross_func(){ $arrear_gross } ?>";
$code = "<?php function $pf_calculation_func(){ $pf_calculation } ?>";
fopen($filename, "w");
file_put_contents($filename,$code);
chmod($filename, 0777);
umask($oldmask);
require_once($filename);
$cumulate_arrear_gross = 0;
$pf_value = $pf_calculation_func();
$cumulate_pf = $cumulate_pf + $pf_value;
$cumulate_arrear_gross = $cumulate_arrear_gross + $arrear_gr;
$upd_arrears_query = 'UPDATE cw_arrears SET arrear_value = "'. $arrear_gr .'",arrear_pf = "'. $pf_value .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'. $created_on .'" WHERE employee_code = "'. $employee_code .'" and increment_apply_month = "'.$apply_month.'" and arrear_column = "'.$arrear_gross_db.'" and arrear_month = "'.$arrear_month.'"';
$upd_arrears_result = $this->db->query("CALL sp_a_run ('UPDATE','$upd_arrears_query')");
$i++;
unlink("$filename");
}
if($upd_arrears_result){
$upd_cumulative_query = 'UPDATE cw_arrear_cumulative SET arrear_value = "'. $cumulate_arrear_gross .'",arrear_pf = "'. $cumulate_pf .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'. $created_on .'" WHERE employee_code = "'. $employee_code .'" and increment_apply_month = "'.$apply_month.'" and arrear_column = "'.$arrear_gross_db.'"';
$upd_cumulative_result = $this->db->query("CALL sp_a_run ('UPDATE','$upd_cumulative_query')");
return true;
}
return true;
}
//CREATE_FORMULA_FILE
public function create_formula_file(){
//file path with server
$filename = dirname(__FILE__)."/"."Increment_calculation_model.php";
$filename = str_replace('controllers','models',$filename);
$can_process = false;
$input_query = 'SELECT line_input_bind_table,line_input_bind_to,line_input_bind_col,condition_check_form,cond_drop_down FROM `cw_form_bind_input` INNER JOIN `cw_form_condition_formula` ON cw_form_condition_formula.prime_cond_id = cw_form_bind_input.input_cond_id
WHERE input_cond_module_id ="increment" AND cond_order != 0 AND line_input_bind_col !="" AND cw_form_condition_formula.trans_status=1';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$input_colum = "";
foreach($input_result as $input){
$role = $input->line_input_bind_table;
$out_colum = $input->line_input_bind_to;
$input_colum = $input->line_input_bind_col;
$cond_drop_down = $input->cond_drop_down;
$condition_check_form = $input->condition_check_form;
$condition_check_form = explode(",",$condition_check_form);
if($input_colum){
foreach($condition_check_form as $check_form){
if(strpos($input_colum,"@$check_form@") !== false){
$value = "\$trans['".$check_form."']";
$input_colum = str_replace("@$check_form@",$value, $input_colum);
$input_colum = str_replace("return","\$trans['".$out_colum."'] = ", $input_colum);
$data .= "'$check_form' => $value,";
}
}
}
if($cond_drop_down){
$formula_code .= "\n\t\t\t if(\$trans['$cond_drop_down'] === '$role'){";
$formula_code .= "\n\t\t\t $input_colum \n\t\t\t";
$formula_code .= "\n\t\t\t } \n\t\t\t";
}else{
$formula_code .= "\n\t\t\t $input_colum \n\t\t\t";
}
}
$fname = 'increment_calculation($trans_array,$employee_code){';
$formula_code = str_replace('~', '"', $formula_code);
$formula_code = "\n<?php class Increment_calculation_model extends CI_Model{\n\tpublic function $fname \n\t\t".' foreach($trans_array as $trans){ '.$formula_code."\n\t\t \$trans_array[\$employee_code] = \$trans;\n\t\t } return \$trans_array; \n\t\t }\n}?>";
$formula_temp_file = dirname(__FILE__)."/"."Increment_calculation_model.php";
$formula_temp_file = str_replace('controllers','models',$formula_temp_file);
fopen("$formula_temp_file", "w");
file_put_contents("$formula_temp_file",$formula_code);
chmod($formula_temp_file, 0777);
umask($oldmask);
}
public function inc_excel($Payload){
//Decryption
$_POST = $this->cryptoDecrypt(base64_decode(urldecode($Payload)));
$module_id = $this->input->post('module_id');
$excel_format = $this->input->post('excel_format');
$category = $this->input->post('category');
$excel_format_qry = 'select template_name,employee_code,effective_date,before_day,after_day,view_name,column_map from cw_inc_temp_setting inner join cw_increment_template on cw_increment_template.temp_name = cw_inc_temp_setting.prime_inc_temp_setting_id inner join cw_form_setting on cw_form_setting.label_name = cw_increment_template.column_name where cw_inc_temp_setting.trans_status = 1 and cw_increment_template.trans_status = 1 and cw_form_setting.trans_status = 1 and cw_inc_temp_setting.module_id = "'.$module_id.'" and cw_inc_temp_setting.prime_inc_temp_setting_id = "'.$excel_format.'" AND cw_increment_template.category = "'.$category.'"';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$get_view_name_query = 'SELECT view_name,label_name,prime_module_id FROM `cw_form_setting` WHERE trans_status = 1 and prime_module_id = "'.$module_id.'"';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_view_name_query')");
$column_rslt = $column_info->result_array();
$column_info->next_result();
$column_rslt = array_reduce($column_rslt, function ($result, $arr) {
$result[$arr['label_name']] = $arr['view_name'];
return $result;
}, array());
$excel_name = str_replace(' ', '_', $excel_result[0]->excel_name);
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
# Set the first row as the header row
$header_arr = array('employee_code','effective_date','before_day','after_day');
foreach($header_arr as $excel){
$excel_line_column_name = ucwords(str_replace('_', ' ', $excel));
$excel_line_value = $excel_result[0]->$excel;
if($excel_line_value){
$worksheet->getCell($excel_line_value.'1')->setValue($excel_line_column_name);
}
}
foreach($excel_result as $excel){
$excel_line_column_name = $excel->view_name;
$excel_line_value = $excel->column_map;
if($excel_line_value){
$worksheet->getCell($excel_line_value.'1')->setValue($excel_line_column_name);
}
}
ob_end_clean();
$excel_name = str_replace(' ', '_', $excel_result[0]->template_name);
$filename = $excel_name.".xls";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename= "'.$filename.'"');
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');
ob_end_clean();
echo json_encode(array('success' => TRUE, 'output' => $worksheet));
exit(0);
}
}
?>