File: /home/cafsindia/hrms_cafsinfotech_in/application/controllers/Monthly_input_posting.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Monthly_input_posting extends Action_controller{
public function __construct(){
parent::__construct('monthly_input_posting');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['encKey'] = $this->generateKey();
$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;
$this->load->view("$this->control_name/manage",$data);
}
public function mi_posting(){
//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);
}
$process_month = $this->input->post('process_month');
$category = $this->input->post('category');
$process_type = (int)$this->input->post('process_type');
$process_emp_code = $this->input->post('hidden_process_emp_code');
$date = DateTime::createFromFormat('m-Y', $process_month);
$date->modify('-1 month');
$previous_month = $date->format('m-Y');
$fandf_processed_emp_qry = 'select employee_code from cw_transactions where process_month="'.$previous_month.'" and termination_status = 1 and fandf = 1';
$fandf_emp_info = $this->db->query("CALL sp_a_run ('SELECT','$fandf_processed_emp_qry')");
$fandf_emp_result = $fandf_emp_info->result();
$fandf_emp_info->next_result();
$fandf_employees = array_column($fandf_emp_result,"employee_code");
$fandf_emp = implode('","',$fandf_employees);
$where_query = "";
if($process_type === 1){
$where_query = ' and employee_code = "'.$process_emp_code.'" ';
}else
if($process_type === 2){
$where_query = ' and role = "'.$category.'"';
}
$posting_lock_qry = 'SELECT COUNT(*) as count FROM cw_payroll_posting_lock WHERE trans_status = 1 and posting_month = "'.$process_month.'" and category = "'.$category.'" and posting_lock = 1';
$posting_lock_info = $this->db->query("CALL sp_a_run ('SELECT','$posting_lock_qry')");
$posting_lock_result = $posting_lock_info->result();
$posting_lock_info->next_result();
$lock_count = $posting_lock_result[0]->count;
if((int)$lock_count > 0){
echo json_encode(array('success' => FALSE, 'message' => "Payroll Posting Locked for This Month!!"));
exit(0);
}
$count = $this->checkisprocessed($process_month,$where_query,'cw_transactions');
if((int)$count > 0){
echo json_encode(array('success' => FALSE, 'message' => "Payroll Already Processed for This Month!!"));
exit(0);
}
//Get salary date info
$tos_salary_dates = $this->tos_sal_strt_end_info("4",$process_month); // 4 => Posting Entry
$start_date = date("Y-m-d",strtotime($tos_salary_dates['salary_start_date']));
$end_date = date("Y-m-d",strtotime($tos_salary_dates['salary_end_date']));
$end_month = date("m-Y",strtotime($salary_date_info['salary_end_date']));
//Get Active Leave fin id
$leave_financial_info = $this->get_leave_financial_details();
$prime_financial_id = $leave_financial_info[0]->prime_leave_financial_year_id;
//Leave Encash Details Update Start
$leave_tbl_query = 'SELECT leave_name,max_limit,encash_type FROM cw_leave_creation inner join cw_leave_encash_settings on FIND_IN_SET(cw_leave_creation.leave_name,cw_leave_encash_settings.leave_type) WHERE cw_leave_encash_settings.trans_status = 1 and leave_opening = 1 and FIND_IN_SET('.$category.', cw_leave_encash_settings.category)';
$leave_tbl_info = $this->db->query("CALL sp_a_run ('SELECT','$leave_tbl_query')");
$columns = $leave_tbl_info->result();
$leave_tbl_info->next_result();
if($columns){
$select_columns = "";
$opening_upd_qry = "";
$encash_ins_qry = "";
$where_cond = "";
foreach ($columns as $key => $value){
$leave_name = strtolower($value->leave_name);
$max_limit = $value->max_limit;
$encash_type = (int)$value->encash_type;
if($leave_name){
if($encash_type === 1 || ($encash_type === 2 && $process_month === $end_month)){
if($max_limit === '0.00'){
$select_columns .= ",(".$leave_name."+".$leave_name."_credit) - (".$leave_name."_debit + used_".$leave_name." + pending_".$leave_name." + encash_".$leave_name.") as encash_".$leave_name;
}else{
$select_columns .= ",IF((".$leave_name."+".$leave_name."_credit) - (".$leave_name."_debit + used_".$leave_name." + pending_".$leave_name." + encash_".$leave_name.") >= $max_limit,$max_limit,(".$leave_name."+".$leave_name."_credit) - (".$leave_name."_debit + used_".$leave_name." + pending_".$leave_name." + encash_".$leave_name.")) AS encash_".$leave_name;
}
$opening_upd_qry .= ' cw_leave_opening.encash_'.$leave_name.' = cw_leave_opening.encash_'.$leave_name.'+tbl_encash_temp.encash_'.$leave_name.' , ';
$encash_ins_qry .= ' ,encash_'.$leave_name;
$where_cond .= ' encash_'.$leave_name.'+';
}
}
}
if($select_columns){
$where_cond = rtrim($where_cond,"+");
$drop_qry = 'DROP TABLE IF EXISTS tbl_encash_temp';
$drop_info = $this->db->query("CALL sp_a_run ('RUN','$drop_qry')");
$temp_qry = 'CREATE TABLE tbl_encash_temp SELECT employee_code'.$select_columns.' from cw_leave_opening where trans_status = 1 and financial_setting_id = "'.$prime_financial_id.'" and component_value = "'.$category.'"';
$temp_info = $this->db->query("CALL sp_a_run ('RUN','$temp_qry')");
if($temp_info){
$encash_insert_qry = 'INSERT into cw_leave_encash_days (financial_setting_id,component_value,employee_code,process_month '.$encash_ins_qry.')SELECT "'.$prime_financial_id.'","'.$category.'",employee_code,"'.$process_month.'" '.$encash_ins_qry.' from tbl_encash_temp where '.$where_cond.' > 0';
$encash_insert_info = $this->db->query("CALL sp_a_run ('RUN','$encash_insert_qry')");
if($encash_insert_info){
$upd_encash_qry = 'UPDATE cw_leave_opening inner join tbl_encash_temp on tbl_encash_temp.employee_code = cw_leave_opening.employee_code SET '.$opening_upd_qry.' cw_leave_opening.trans_updated_by = "'.$this->logged_id.'",cw_leave_opening.trans_updated_date = "'.$created_on.'" WHERE cw_leave_opening.financial_setting_id = "'.$prime_financial_id.'" and cw_leave_opening.employee_code = tbl_encash_temp.employee_code and cw_leave_opening.trans_status = 1';
$upd_encash_info = $this->db->query("CALL sp_a_run ('RUN','$upd_encash_qry')");
}
}
}
}
//Leave Encash Details Update END
// THIS WILL UPDATE HAS ACHIVE ON PROCEDURE WAY SO NO NEED _ARN 30-01-2026
//Posting Coff Data
// if($category && $process_month){
// $this->coff_posting($category,$process_month,$prime_financial_id);
// }
//MI MAPPING QUERY
$mapping_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT time_office_component,mi_component FROM cw_monthly_input_mapping WHERE trans_status = 1')");
$mapping_result = $mapping_info->result_array();
$mapping_info->next_result();
$time_office_components = array_column($mapping_result, 'time_office_component');
$mi_components = array_column($mapping_result, 'mi_component');
$posting_columns = array_combine( $mi_components, $time_office_components);
//Get from company info module.
$company_info = $this->company_info();
$coff_based_on = $company_info[0]->coff_based_on;
$coff_check_qry = "";
if($coff_based_on !== 'role' && $coff_based_on !== '0'){
$coff_check_qry = ' ,'.$coff_based_on;
}
//Get Employee Details //OLD QUERY (RAGU COMMENT THIS BECAUSE OF TERMINATION STATUS)
// $emp_qry = 'SELECT cw_employees.employee_code,cw_employees.emp_name,cw_employees.termination_status,date_of_joining,last_working_date,prime_employees_id,role '.$coff_check_qry.' FROM `cw_employees` where cw_employees.trans_status = 1 and (cw_employees.termination_status = 0 or cw_employees.resignation_date >= "'.$start_date.'") '.$where_query;
//New query
$emp_qry = 'SELECT cw_employees.employee_code,cw_employees.emp_name,cw_employees.termination_status,date_of_joining,last_working_date,prime_employees_id,role '.$coff_check_qry.' FROM `cw_employees` where cw_employees.trans_status = 1 and (cw_employees.termination_status = 0 or DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") between "' . $start_date . '" and "' . $end_date . '") and employee_code NOT IN ("'.$fandf_emp.'") '.$where_query;
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_rslt = $emp_info->result_array();
$emp_info->next_result();
$emp_rslt_arr = array_reduce($emp_rslt, function($result, $arr){
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
$result = $this->get_muster_details($emp_rslt_arr,$start_date,$end_date,$category,$emp_component,$posting_columns,'posting',$process_month,$process_type);
if($result){
echo json_encode(array('success' => TRUE, 'message' => "Posting Completed Successfully..!!"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Please try After sometime!!"));
}
}
public function emp_suggest(){
//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);
}
$search_term = $this->input->post('term');
$final_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 and (employee_code like "'.$search_term.'%" or emp_name like "%'.$search_term.'%")';
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$suggestions[] = array('value' => $employee_code, 'label' => "$employee_code - $emp_name");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
//Coff Posting
// public function coff_posting($category,$posting_month,$prime_financial_id){
// //Get leave name
// $leave_creation_qry = 'select leave_name from cw_leave_creation where coff = 1 and trans_status = 1';
// $leave_creation_info = $this->db->query("CALL sp_a_run ('SELECT','$leave_creation_qry')");
// $leave_creation_result = $leave_creation_info->result();
// $leave_creation_info->next_result();
// $leave_name = strtolower($leave_creation_result[0]->leave_name);
// if($leave_name){
// /*$coff_based_on = $this->company_info[0]->coff_based_on; */
// //Get from company info module.
// $company_info = $this->company_info();
// $coff_based_on = $company_info[0]->coff_based_on;
// if($coff_based_on !== 'role' && $coff_based_on !== '0'){
// $coff_check_qry = ' and FIND_IN_SET(em.'.$coff_based_on.',cs.check_input)';
// }
// $salary_dates = $this->salary_start_end_info($category,$posting_month);
// $salary_start_date = date("Y-m-d",strtotime($salary_dates['salary_start_date']));
// $salary_end_date = date("Y-m-d",strtotime($salary_dates['salary_end_date']));
// $coff_entry_qry = 'select COUNT(*) as count from cw_coff_entry where credited_month = "'.$posting_month.'" and financial_setting_id = "'.$prime_financial_id.'" and trans_status = 1';
// $coff_entry_info = $this->db->query("CALL sp_a_run ('SELECT','$coff_entry_qry')");
// $coff_entry_result = $coff_entry_info->result();
// $coff_entry_info->next_result();
// $count = $coff_entry_result[0]->count;
// if((int)$count === 0){
// $insert_qry = 'INSERT into cw_coff_entry (employee_code,category,coff_mins,credited_date,end_date,coff_count,balance_count,ot_hours,max_ot_hours,coff_hours,credited_month,fullday_min,halfday_min,financial_setting_id,trans_created_by,trans_created_date) SELECT em.employee_code,role as category,IF(SUM(approved_ot_mins+approved_shift_ot+approved_special_ot) > max_ot_mins,SUM(approved_ot_mins+approved_shift_ot+approved_special_ot)- max_ot_mins,0) as coff_mins,now() as credited_date,DATE_ADD(now(), INTERVAL expiry_days DAY) as end_date,IF(FLOOR((IF(SUM(approved_ot_mins+approved_shift_ot+approved_special_ot) > max_ot_mins,SUM(approved_ot_mins+approved_shift_ot+approved_special_ot)- max_ot_mins,0))/cs.fullday_min) >= 1,FLOOR((IF(SUM(approved_ot_mins+approved_shift_ot+approved_special_ot) > max_ot_mins,SUM(approved_ot_mins+approved_shift_ot+approved_special_ot)- max_ot_mins,0))/cs.fullday_min),0) + IF(FLOOR(((IF(SUM(approved_ot_mins+approved_shift_ot+approved_special_ot) > max_ot_mins,SUM(approved_ot_mins+approved_shift_ot+approved_special_ot)- max_ot_mins,0)) MOD cs.fullday_min)/cs.halfday_min) >= 1,0.5,0) as coff_count,IF(FLOOR((IF(SUM(approved_ot_mins+approved_shift_ot+approved_special_ot) > max_ot_mins,SUM(approved_ot_mins+approved_shift_ot+approved_special_ot)- max_ot_mins,0))/cs.fullday_min) >= 1,FLOOR((IF(SUM(approved_ot_mins+approved_shift_ot+approved_special_ot) > max_ot_mins,SUM(approved_ot_mins+approved_shift_ot+approved_special_ot)- max_ot_mins,0))/cs.fullday_min),0) + IF(FLOOR(((IF(SUM(approved_ot_mins+approved_shift_ot+approved_special_ot) > max_ot_mins,SUM(approved_ot_mins+approved_shift_ot+approved_special_ot)- max_ot_mins,0)) MOD cs.fullday_min)/cs.halfday_min) >= 1,0.5,0) as balance_count,time_format(concat(floor((approved_ot_mins+approved_shift_ot+approved_special_ot)/60),":",lpad(mod(approved_ot_mins+approved_shift_ot+approved_special_ot,60),2,"0")),"%H:%i") as ot_hours,max_ot_hours,time_format(concat(floor(IF(SUM(approved_ot_mins+approved_shift_ot+approved_special_ot) > max_ot_mins,SUM(approved_ot_mins+approved_shift_ot+approved_special_ot)- max_ot_mins,0)/60),":",lpad(mod(IF(SUM(approved_ot_mins+approved_shift_ot+approved_special_ot) > max_ot_mins,SUM(approved_ot_mins+approved_shift_ot+approved_special_ot)- max_ot_mins,0),60),2,"0")),"%H:%i") as coff_hours,"'.$posting_month.'" as credited_month,fullday_min,halfday_min,"'.$prime_financial_id.'","'.$this->logged_id.'",now() from cw_employees em JOIN cw_coff_settings cs ON(FIND_IN_SET(em.role,cs.category) '.$coff_check_qry.') join cw_time_entry on cw_time_entry.employee_code = em.employee_code and att_date BETWEEN "'.$salary_start_date.'" and "'.$salary_end_date.'" and coff_activated = 1 and max_ot_hours > "0.00" group by prime_employees_id having coff_count > "0.00"';
// $insert_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_qry')");
// $insert_result = $insert_info->result();
// $insert_info->next_result();
// if($insert_result){
// $upd_coff_entry_qry = 'UPDATE cw_leave_opening inner join cw_coff_entry on cw_coff_entry.employee_code = cw_leave_opening.employee_code SET '.$leave_name.'_credit = '.$leave_name.'_credit+cw_coff_entry.coff_count,cw_leave_opening.trans_updated_by = "'.$this->logged_id.'",cw_leave_opening.trans_updated_date = "'.$created_on.'" WHERE cw_leave_opening.financial_setting_id = "'.$prime_financial_id.'" and cw_leave_opening.trans_status = 1 and cw_coff_entry.trans_status = 1';
// $upd_coff_entry_info = $this->db->query("CALL sp_a_run ('RUN','$upd_coff_entry_qry')");
// return true;
// }
// }else{
// return true;
// }
// }else{
// return true;
// }
// }
}
?>