File: /home/cafsindia/hrms_cafsinfotech_in/application/controllers/Leave_mi_posting.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Leave_mi_posting extends Action_controller{
public function __construct(){
parent::__construct('leave_mi_posting');
$this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['quick_link'] = $this->quick_link;
$data['table_head'] = $this->table_head;
$data['master_pick'] = $this->master_pick;
$data['fliter_list'] = $this->fliter_list;
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id != 1')");
$role_result = $role_info->result();
$role_info->next_result();
$category_list[""] = "---- Select Category ----";
foreach ($role_result as $for) {
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$this->load->view("$this->control_name/manage",$data);
}
public function posting_month_validate(){
$posting_month = $this->input->post('process_month');
$category = $this->input->post('category');
// echo "$process_month";die;
$posting_lock_qry = 'SELECT count(*) as count from cw_payroll_posting_lock where cw_payroll_posting_lock.trans_status = 1 and posting_month >="'.$posting_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();
$posting_lock_count = $posting_lock_result[0]->count;
if($posting_lock_count){
echo json_encode(array('success' => FALSE, 'message' => "Payroll Posting Locked for This Month!!"));
}else{
echo json_encode(array('success' => TRUE));
}
}
public function mi_leave_posting(){
$process_month = $this->input->post('process_month');
$category = $this->input->post('category');
$process_date = "01-".$process_month;
$process_date = new DateTime($process_date);
$month = $process_date->format('n');
$year = $process_date->format('Y');
$financial_info = $this->leave_financial_info;
$financial_id = $financial_info[0]->prime_leave_financial_year_id;
$overall_leave_array = "";
$employee_code_result_arr = "";
$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);
}
$month_day_info = $this->db->query("CALL sp_a_run ('SELECT','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_result = $month_day_info->result();
$month_day_info->next_result();
if($month_day_result){
$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;
$today_date = date("Y-m-d");
if((int)$day_conditions === 3){
$sal_start = $day_start;
//For Current month between days increment
$date = new DateTime("01-$process_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."-".$process_month));
}else{
$sal_start = '01';
$date = new DateTime("01-$process_month 00:00:00");
$salary_start_date = $date->format("Y-m-$sal_start");
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}
//ALL EMPLOYEES SELECT QUERY FROM EMPLOYEE MASTER TABLE
$employee_code_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT employee_code,emp_name FROM cw_employees WHERE trans_status = 1 and user_right not in (1,2) and role = $category ')");
$employee_code_result = $employee_code_info->result_array();
$employee_code_info->next_result();
$employee_code_result_arr = array_reduce($employee_code_result, function($result, $arr) {
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
//LEAVE MAPPING QUERY
$leave_mapping_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT online_component,offline_component FROM cw_leave_mapping WHERE trans_status = 1')");
$leave_mapping_result = $leave_mapping_info->result();
$leave_mapping_info->next_result();
//LEAVE CREATION QRY FOR GET A ALL LEAVE NAME
$leave_creation_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT prime_leave_creation_id,lower(leave_name) as leave_name,leave_opening FROM cw_leave_creation WHERE trans_status = 1')");
$leave_creation_result = $leave_creation_info->result_array();
$leave_creation_info->next_result();
$leave_creation_count = count($leave_creation_result);
$leave_name_arr = array_reduce($leave_creation_result, function($result, $arr) {
$result[$arr['leave_name']] = $arr['leave_opening'];
return $result;
}, array());
$select_columns = "";
$debit_columns = "";
$credit_columns = "";
foreach ($leave_creation_result as $key => $value){
$leave_id = $value['prime_leave_creation_id'];
$leave_name = $value['leave_name'];
$leave_opening = $value['leave_opening'];
if($leave_name && (int)$leave_opening === 1){
$select_columns .= "SUM(CASE WHEN leave_type = $leave_id THEN leave_count ELSE 0 END) used_".$leave_name.",cw_leave_opening.$leave_name,";
$debit_columns .= 'SUM(CASE WHEN leave_type = "'.$leave_name.'" THEN number_of_days ELSE 0 END) '.$leave_name.'_debit,';
$credit_columns .= 'SUM(CASE WHEN leave_name = "'.$leave_name.'" THEN monthly_credit ELSE 0 END) '.$leave_name.'_credit,';
}else{
$select_columns .= "SUM(CASE WHEN leave_type = $leave_id THEN leave_count ELSE 0 END) ".$leave_name.",";
}
}
$select_columns = rtrim($select_columns,",");
$debit_columns = rtrim($debit_columns,",");
$credit_columns = rtrim($credit_columns,",");
$leave_entry_qry = 'select '.$select_columns.',cw_leave_entry.employee_code as employee_code from cw_leave_entry inner join cw_leave_opening on cw_leave_opening.employee_code = cw_leave_entry.employee_code where cw_leave_entry.trans_status = 1 and cw_leave_entry.leave_status = 2 and cw_leave_entry.component_value = "'.$category.'" AND cw_leave_entry.leave_date <= "'.$salary_end_date.'" group by cw_leave_entry.employee_code';
$leave_entry_info = $this->db->query("CALL sp_a_run ('SELECT','$leave_entry_qry')");
$leave_entry_result = $leave_entry_info->result_array();
$leave_entry_info->next_result();
$leave_entry_result = array_reduce($leave_entry_result, function($result, $arr) {
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
$leave_credit_qry = 'select cw_leave_cr_upd_log.employee_code as employee_code, '.$credit_columns.' from cw_leave_cr_upd_log where cw_leave_cr_upd_log.trans_created_date <= "'.$salary_end_date.'" group by cw_leave_cr_upd_log.employee_code';
$leave_credit_info = $this->db->query("CALL sp_a_run ('SELECT','$leave_credit_qry')");
$leave_credit_result = $leave_credit_info->result_array();
$leave_credit_info->next_result();
$leave_credit_result = array_reduce($leave_credit_result, function($result, $arr) {
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
$leave_debit_qry = 'select employee_code,'.$debit_columns.' from cw_debit_leave where cw_debit_leave.entry_date <= "'.$salary_end_date.'" and financial_setting_id = "'.$financial_id.'" group by cw_debit_leave.employee_code';
$leave_debit_info = $this->db->query("CALL sp_a_run ('SELECT','$leave_debit_qry')");
$leave_debit_result = $leave_debit_info->result_array();
$leave_debit_info->next_result();
$leave_debit_result = array_reduce($leave_debit_result, function($result, $arr) {
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
$final_leave_array = array_replace_recursive($employee_code_result_arr,$leave_entry_result, $leave_credit_result, $leave_debit_result);
$balance_arr = array();
foreach ($final_leave_array as $key => $value){
foreach ($leave_name_arr as $leave_name => $opening){
if((int)$opening === 1){
if($value[$leave_name]){
$op = $value[$leave_name];
}else{
$op = '0.00';
$final_leave_array[$key][$leave_name] = $op;
}
if($value[$leave_name.'_credit']){
$credit = $value[$leave_name.'_credit'];
}else{
$credit = '0.00';
$final_leave_array[$key][$leave_name.'_credit'] = $credit;
}
if($value[$leave_name.'_debit']){
$debit = $value[$leave_name.'_debit'];
}else{
$debit = '0.00';
$final_leave_array[$key][$leave_name.'_debit'] = $debit;
}
if($value['used_'.$leave_name]){
$used = $value['used_'.$leave_name];
}else{
$used = '0.00';
$final_leave_array[$key]['used_'.$leave_name] = $used;
}
$balance = number_format(($op + $credit) - ($debit+$used), 2);
$final_leave_array[$key]['balance_'.$leave_name] = $balance;
}
}
}
$leave_mi_upd_qry = "";
foreach ($final_leave_array as $key => $value){
$leave_mi_upd_qry .= '"'.$value['employee_code'].'","'.$process_month.'",';
foreach ($leave_mapping_result as $mapping_key => $mapping_value){
$online = $mapping_value->online_component;
$offline = $mapping_value->offline_component;
$leave_mi_upd_qry .= '"'.$value[$online].'",';
}
$leave_mi_upd_qry .= '"'.$this->logged_id.'",'.'"'.date("Y-m-d H:i:s").'"';
$leave_mi_upd_qry .= "),(";
}
$leave_mi_upd_qry = rtrim($leave_mi_upd_qry,"),(");
$upd_mi_query = 'INSERT INTO cw_monthly_input (employee_code,process_month,ld,casual_leave,cl_op,el,el_op,hd,trans_updated_by,trans_updated_date) VALUES ('.$leave_mi_upd_qry.') ON DUPLICATE KEY UPDATE employee_code = IF(employee_code === VALUES(employee_code), VALUES(employee_code), employee_code)(VALUES(employee_code),process_month = VALUES(process_month),ld = VALUES(ld),casual_leave = VALUES(casual_leave),cl_op = VALUES(cl_op),el = VALUES(el),el_op = VALUES(el_op),hd = VALUES(hd),trans_updated_by = VALUES(trans_updated_by),trans_updated_date = VALUES(trans_updated_date)';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_mi_query')");
if(!$leave_entry_result){
echo json_encode(array('success' => FALSE, 'message' => "No data found for this month...!!"));
}else{
$posting_lock_qry = 'SELECT COUNT(*) as count FROM cw_payroll_posting_lock WHERE trans_status = 1 and posting_month = "'.$process_month.'" and category = "'.$category.'"';
$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;
$created_on = date("Y-m-d H:i:s");
if((int)$lock_count > 0){
$update_query = 'UPDATE cw_payroll_posting_lock SET posting_lock = "1",trans_updated_by = "'.$this->logged_id.'", trans_updated_date = "'.$created_on.'" WHERE category = "'. $category .'" and posting_month = "'. $process_month.'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
echo json_encode(array('success' => TRUE, 'message' => "Data Successfully Updated"));
}else{
$insert_posting_lock_query = 'insert into cw_payroll_posting_lock (category,posting_month,posting_lock,trans_created_by,trans_created_date) values ("'.$category.'","'.$process_month.'","1","'.$this->logged_id.'","'.$created_on.'")';
$posting_lock_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_posting_lock_query')");
$posting_lock_result = $posting_lock_info->result();
$posting_lock_info->next_result();
echo json_encode(array('success' => TRUE, 'message' => "Data Successfully Inserted"));
}
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Month day not set for this category...!!"));
}
}
}
?>