File: /home/cafsindia/hrms_allyindian_com/application_bk/controllers/Full_and_final_settlement.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Full_and_final_settlement extends Action_controller{
public function __construct(){
parent::__construct('full_and_final_settlement');
$this->create_formula_file();
$this->load->model('Fandf_calculation_model');
$this->load->model('Process_payroll_model');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$data['table_head'] = (object)array("category_name"=>"Category","employee_code"=>"Employee Code","emp_name"=>"Name","resignation_date"=>"Resignation Date","last_working_date"=>"Last Working Date");
$this->load->view("full_and_final_settlement/manage",$data);
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
public function search(){
$dec_data = $this->cryptoDecrypt($_POST['Payload']);
$_POST = $dec_data['data'];
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....'));
exit(0);
}
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$order = $this->input->post('order');
$order_col = $this->input->post('columns');
$search = $this->input->post('search');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$order_col = $order_col[$column]['data'];
$search = trim($search['value']);
$filter_count = count($fliter_label ?? []);
$fliter_query = "";
$common_search = "";
if($search){
$common_search = ' and (category_name like "'.$search.'%" or employee_code like "'.$search.'%" or emp_name like "'.$search.'%")';
}
$search_query ="select employee_code,emp_name,category_name,resignation_date,last_working_date from cw_employees inner join cw_category on cw_category.prime_category_id= cw_employees.role where cw_employees.trans_status=1 and cw_employees.termination_status = 1 $common_search ";
$search_query .= "ORDER BY last_working_date DESC";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$total_count = $search_data->num_rows();
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $total_count,"data" => $search_result));
}
public function view($employee_code){
$data['employee_code']= $employee_code;
//common settings for notice period company, single entry only.
$notice_period_qry = 'select notice_period from cw_notice_period where trans_status = 1';
$notice_period_data = $this->db->query("CALL sp_a_run ('SELECT','$notice_period_qry')");
$notice_period_result = $notice_period_data->result();
$notice_period_data->next_result();
$notice_period = $notice_period_result[0]->notice_period;
$data['notice_period'] = $notice_period;
//Find Column in Monthly Input
$notice_period_serve_qry = 'select DATEDIFF(last_working_date,resignation_date) as notice_served_day from cw_employees where trans_status = 1 and employee_code="'.$employee_code.'"';
$notice_period_serve_data = $this->db->query("CALL sp_a_run ('SELECT','$notice_period_serve_qry')");
$notice_period_serve_result = $notice_period_serve_data->result();
$notice_period_serve_data->next_result();
$notice_served_day = $notice_period_serve_result[0]->notice_served_day;
$notice_served_day = (int)$notice_served_day + 1; //Include last working days
$data['notice_served_day'] = $notice_served_day;//fixed find notice served day
$get_last_working_day = 'select last_working_date,role from cw_employees where trans_status = 1 and employee_code="'.$employee_code.'"';
$get_last_working_data = $this->db->query("CALL sp_a_run ('SELECT','$get_last_working_day')");
$get_last_working_result = $get_last_working_data->result();
$get_last_working_data->next_result();
$last_working_date = $get_last_working_result[0]->last_working_date;
$role = $get_last_working_result[0]->role;
$pending_notice_day = $notice_period - $notice_served_day;//notice period recovery day.
//Get Month Days start and end date
$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 ="'.$role.'"';
$month_day_data = $this->db->query("CALL sp_a_run ('SELECT','$month_day_qry')");
$month_day_result = $month_day_data->result();
$month_day_data->next_result();
if($month_day_result){
$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){
$prev_month = date("Y-m-".$day_start,strtotime("-1 month", strtotime($last_working_date)));
$end_month = date("Y-m-".$day_end, strtotime($last_working_date));
}else{
$sal_start = '01';
if((int)$day_conditions === 2){
$day_end = date("t");
}
$prev_month = date("Y-m-".$sal_start,strtotime($last_working_date));
$end_month = date("Y-m-".$day_end,strtotime($last_working_date));
}
}
$end_date = strtotime($end_month);
$start_date = strtotime($prev_month);
$last_work_date = strtotime($last_working_date);
if(($last_work_date >= $start_date) && ($last_work_date <= $end_date)){
$last_month = date("m-Y",strtotime($last_working_date));
}else{
$last_month_date = date("Y-m-".$day_start,strtotime("+1 month", strtotime($last_working_date)));
$last_month = date("m-Y",strtotime($last_month_date));
}
$data['last_month'] = $last_month;
//Monthly Input updates
$get_emp_data_qry = 'select * from cw_employees where trans_status = 1 and employee_code="'.$employee_code.'"';
$get_emp_data_info = $this->db->query("CALL sp_a_run ('SELECT','$get_emp_data_qry')");
$get_emp_data_result = $get_emp_data_info->result();
$get_emp_data_info->next_result();
$data['get_emp_data_result'] = $get_emp_data_result[0];
if($get_emp_data_result){
$emp_id = $get_emp_data_result[0]->prime_employees_id;
$emp_code = $get_emp_data_result[0]->employee_code;
$emp_name = $get_emp_data_result[0]->emp_name;
$doj = $get_emp_data_result[0]->date_of_joining;
$termination_status = $get_emp_data_result[0]->termination_status;
$role = $get_emp_data_result[0]->role;//category
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
//gratuity calculation for fandf process
$gratuity_rslt = $this->Process_payroll_model->get_gratuity($emp_code,$role);
//notice period recover day notice minus last working day.
if($pending_notice_day > 0){
$pending_notice_val = $pending_notice_day;
}else{
$pending_notice_val = 0;
}
//monthly input insert for fandf employees
$prime_qry_key .= "employees_id,emp_name,role,employee_code,date_of_joining,process_month,termination_status,gratuity,notice_period_recovery_days,trans_created_by,trans_created_date";
$prime_qry_value .= '"'.$emp_id.'","'.$emp_name.'","'.$role.'","'.$emp_code.'","'.$doj.'","'.$last_month.'","'.$termination_status.'","'.$gratuity_rslt.'","'.$pending_notice_val.'","'.$logged_id.'","'.$today_date.'"';
$term_sts_exit_qry = 'select count(*) as rslt_count from cw_monthly_input where employee_code = "'.$employee_code.'" and trans_status = 1 and termination_status = 1 and process_month= "'.$last_month.'"';
$term_sts_exit_data = $this->db->query("CALL sp_a_run ('SELECT','$term_sts_exit_qry')");
$term_sts_exit_rslt = $term_sts_exit_data->result();
$term_sts_exit_data->next_result();
$term_sts = $term_sts_exit_rslt[0]->rslt_count;
//already exit in monthly input
if((int)$term_sts === 0){
$insert_query = "insert into cw_monthly_input ($prime_qry_key) value ($prime_qry_value)";
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
}
}
//monthly input details fetch last working date based
$monthly_input_qry = 'select * from cw_monthly_input where employee_code="'.$employee_code.'" and termination_status = 1 and trans_status = 1 and process_month="'.$last_month.'"';
$monthly_input_info = $this->db->query("CALL sp_a_run ('SELECT','$monthly_input_qry')");
$monthly_input_rslt = $monthly_input_info->result();
$monthly_input_info->next_result();
//Get Loan Details
$loan_column_qry = 'SELECT GROUP_CONCAT(label_name) as loan_columns FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND input_view_type IN (1,2) and loan_check = 1 ORDER BY prime_form_id';
$loan_column_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_column_qry')");
$loan_column_rslt = $loan_column_data->result();
$loan_column_data->next_result();
$loan_columns = explode(",",$loan_column_rslt[0]->loan_columns);
$paid_qry = 'select emp_code,IFNULL(sum(install_amount),0) as paid from cw_loan_installment where paid_status = 1 and trans_status=1 and emp_code = "'.$employee_code.'"';
$paid_data = $this->db->query("CALL sp_a_run ('SELECT','$paid_qry')");
$paid_result = $paid_data->result_array();
$paid_data->next_result();
$result = array_map(function($v){
return [$v['emp_code'] => $v['paid']];
}, $paid_result);
//Convert Multi dimentional array to single array
$loan_paid = call_user_func_array('array_merge', $result);
$loan_qry = 'SELECT loan_type,loan_amount FROM `cw_loan` where emp_code = "'.$employee_code.'" and trans_status = 1 group by emp_code,loan_type';
$loan_info = $this->db->query("CALL sp_a_run ('SELECT','$loan_qry')");
$loan_result = $loan_info->result_array();
$loan_info->next_result();
$loan_result = array_reduce($loan_result, function ($result, $arr) {
$result[$arr['emp_code']][$arr['loan_type']] = $arr;
return $result;
}, array());
foreach($loan_result as $emp_code => $loan_rslt){
foreach ($loan_columns as $key => $loan_column){
$monthly_input_rslt[0]->$loan_column = $loan_rslt[$loan_column]['loan_amount'] - ($loan_paid[$emp_code] + $loan_rslt[$loan_column]['install_amount']);
}
}
$data['monthly_input_rslt'] = $monthly_input_rslt;
if($employee_code){
$upd_query = 'UPDATE cw_loan_installment SET paid_status = 1 where emp_code = "'.$employee_code.'" and trans_status=1';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
}
//pending payment details for stop payment type
$pending_pay_qry = 'select transactions_month as pay_month,net_pay from cw_employees_stop_payment left join cw_transactions on cw_transactions.employees_id = cw_employees_stop_payment.prime_employees_id where cw_transactions.transactions_month = date_format(cw_employees_stop_payment.stop_pay_month, "%m-%y") and cw_transactions.employee_code = "'.$employee_code.'" and cw_employees_stop_payment.stop_payment_type = 1 and cw_transactions.trans_status = 1';
$pending_pay_info = $this->db->query("CALL sp_a_run ('SELECT','$pending_pay_qry')");
$pending_pay_result = $pending_pay_info->result();
$pending_pay_info->next_result();
$data['pending_pay_result'] = $pending_pay_result;
$this->load->view("full_and_final_settlement/form",$data);
}
public function save_fandf(){
$emp_id = $this->input->post('emp_id');
$emp_code = $this->input->post('emp_code');
$settlement_date = $this->input->post('settlement_date');
$tot_notice = $this->input->post('tot_notice');
$notice_period_served = $this->input->post('notice_period_served');
$salary_days = $this->input->post('salary_days');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
$settlement_date = date("Y-m-d",strtotime($settlement_date));
$fanf_exit_query = 'select count(*) as result_count from cw_fandf_process where trans_status = 1 and emp_code = "'.$emp_code.'"';
$fanf_exit_info = $this->db->query("CALL sp_a_run ('SELECT','$fanf_exit_query')");
$fanf_exit_result = $fanf_exit_info->result();
$fanf_exit_info->next_result();
$exit_check_count = $fanf_exit_result[0]->result_count;
$fanf_exit_trans_query = 'select count(*) as trans_result_count from cw_transactions where trans_status = 1 and termination_status =1 and employee_code = "'.$emp_code.'"';
$fanf_exit_trans_info = $this->db->query("CALL sp_a_run ('SELECT','$fanf_exit_trans_query')");
$fanf_exit_trans_result = $fanf_exit_trans_info->result();
$fanf_exit_trans_info->next_result();
$trans_result_count = $fanf_exit_trans_result[0]->trans_result_count;
if((int)$exit_check_count === 0){
$fandf_insert_query = 'insert into cw_fandf_process (employees_id,emp_code,settlement_date,tot_notice,notice_period_served,salary_days,trans_created_by,trans_created_date) value ("'.$emp_id.'","'.$emp_code.'","'.$settlement_date.'","'.$tot_notice.'","'.$notice_period_served.'","'.$salary_days.'","'.$logged_id.'","'.$today_date.'")';
$fandf_insert_info = $this->db->query("CALL sp_a_run ('INSERT','$fandf_insert_query')");
$fandf_insert_result = $fandf_insert_info->result();
$fandf_insert_info->next_result();
echo json_encode(array('success' => true, 'message' => "Successfully first process updated!"));
}else
if((int)$trans_result_count === 0){
echo json_encode(array('success' => false, 'message' => "Already FandF is initiated, please proceed next step?"));
}
}
public function save_monthly_input(){
$employee_code = $this->input->post('employee_code');
$monthly_id = $this->input->post('monthly_id');
$process_month = $this->input->post('process_month');
$monthly_form_data = $this->input->post('monthly_form_data');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
$upd_query = "";
$insert_key = "";
$insert_val = "";
foreach($monthly_form_data as $monthly_rslt){
$col_name = $monthly_rslt['name'];
$col_value = $monthly_rslt['value'];
$upd_query .= $col_name.' = "'.$col_value.'",';
$insert_key .= $col_name.',';
$insert_val .= '"'.$col_value.'",';
}
//Check monthy input data exist Start
$month_input_exist_qry = 'select employee_code from cw_monthly_input where trans_status = 1 and cw_monthly_input.employee_code = "'.$employee_code.'" and process_month = "'.$process_month.'" and cw_monthly_input.termination_status = 1 limit 0,1';
$month_input_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$month_input_exist_qry')");
$month_input_exist_rslt = $month_input_exist_data->result();
$month_input_exist_data->next_result();
$month_input_exist_count = (int)$month_input_exist_data->num_rows();
if($month_input_exist_count === 0){
$insert_key .= "trans_created_by,trans_created_date";
$insert_val .= '"'.$logged_id.'",'.'"'.$today_date.'"';
$prime_insert_query = "insert into cw_monthly_input ($insert_key) values ($insert_val)";
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
if($insert_result){
echo json_encode(array('success' => TRUE, 'message' => "Successfully added", 'insert_id' => $insert_id));
}else{
echo json_encode(array('success' => false, 'message' => "Unable to process your request"));
}
}else{
$upd_query .= 'trans_updated_by = "'. $logged_id .'",trans_updated_date = "'.$today_date.'"';
$upd_query = 'UPDATE cw_monthly_input SET '. $upd_query .' WHERE prime_monthly_input_id = "'. $monthly_id.'" and employee_code = "'.$employee_code.'" and process_month="'.$process_month.'"';
//echo $upd_query;die;
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
if($upd_query){
echo json_encode(array('success' => true, 'message' => "Updated successfully monthly input!!!"));
}else{
echo json_encode(array('success' => false, 'message' => "Unable to process your request"));
}
}
}
public function fandf_process(){
$employee_code = $this->input->post('employee_code');
$category = $this->input->post('category');
$process_month = $this->input->post('process_month');
$fandf_check_qry = 'select count(*) as rslt_count from cw_transactions where trans_status= 1 and employee_code ="'.$employee_code.'" and transactions_month ="'.$process_month.'"';
$fandf_check_info = $this->db->query("CALL sp_a_run ('SELECT','$fandf_check_qry')");
$fandf_check_result = $fandf_check_info->result();
$fandf_check_info->next_result();
$fandf_count = $fandf_check_result[0]->rslt_count;
if((int)$fandf_count === 0){
$column_qry = 'Select * from cw_form_setting where prime_module_id = "employees" and trans_status = 1 and input_view_type in (1,2)';
$column_data = $this->db->query("CALL sp_a_run ('SELECT','$column_qry')");
$column_rslt = $column_data->result();
$column_data->next_result();
foreach($column_rslt as $column){
$earn_month_check = $column->earn_month_check;
$deduction_month_check = $column->deduction_month_check;
$label_name = $column->label_name;
if(((int)$earn_month_check === 1) || ((int)$deduction_month_check === 1)){
$select_col .= "cw_monthly_input.".$label_name." as $label_name,";
}else{
$select_col .= "cw_employees.".$label_name." as $label_name,";
}
}
$column_label = rtrim($select_col,',');
$emp_info_query = 'select cw_employees.prime_employees_id as employees_id,cw_employees.employee_code,'.$column_label.' from cw_employees inner join cw_monthly_input on cw_employees.employee_code = cw_monthly_input.employee_code where cw_employees.trans_status = 1 and cw_employees.role != 1 and cw_monthly_input.trans_status = 1 and cw_monthly_input.process_month = "'.$process_month.'" and cw_employees.employee_code = "'.$employee_code.'"';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_info_query')");
$emp_rslt = $emp_data->result_array();
$emp_data->next_result();
//array with values of employee and monthly input
$trans_array = array();
foreach($emp_rslt as $rslt){
$employee_code = $rslt['employee_code'];
$trans_array[$employee_code] = $rslt;
}
$fandf_rslt = $this->Fandf_calculation_model->Fandf_calculation($trans_array);
if($fandf_rslt){
//$total_payment = $this->total_amt($employee_code,$process_month);
$table_data = $this->transaction_data($process_month,$employee_code);
echo json_encode(array('success' => TRUE,'message' =>"F and F Successfully completed!!!",'table_content'=>$table_data));
}else{
echo json_encode(array('success' => False,'message' =>"Please Try After Sometime!!!"));
}
}else{
echo json_encode(array('success' => False,'message' =>"Delete the Fandf Process first!!!"));
}
}
public function create_formula_file(){ //file path with server
$map_qry = 'select formula_for,out_column,payroll_formula,formula_mode from cw_payroll_formula where cw_payroll_formula.trans_status = 1';
$data = $this->db->query("CALL sp_a_run ('SELECT','$map_qry')");
$map_result = $data->result();
$data->next_result();
$map_list = array();
foreach($map_result as $map_result_map){
$formula_for = $map_result_map->formula_for;
$out_column = $map_result_map->out_column;
$payroll_formula = $map_result_map->payroll_formula;
$formula_mode = $map_result_map->formula_mode;
$this->sort_formula($formula_for,$out_column,$payroll_formula,$formula_mode);
}
$filename = dirname(__FILE__)."/"."Fandf_calculation_model.php";
$filename = str_replace('controllers','models',$filename);
$can_process = false;
if(file_exists($filename)){
$created_date = date("Y-m-d H:i:s",filemtime($filename));
$isupdated_qry = 'SELECT count(*) as tot_count FROM cw_payroll_formula WHERE trans_created_date >= "'.$created_date.'" or trans_updated_date >= "'.$created_date.'"';
$isupdated_data = $this->db->query("CALL sp_a_run ('SELECT','$isupdated_qry')");
$isupdated_result = $isupdated_data->result();
$isupdated_data->next_result();
$tot_count = (int)$isupdated_result[0]->tot_count;
$loan_updated_qry = 'SELECT count(*) as tot_count FROM cw_loan WHERE trans_created_date >= "'.$created_date.'" or trans_updated_date >= "'.$created_date.'"';
$loan_updated_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_updated_qry')");
$loan_updated_result = $loan_updated_data->result();
$loan_updated_data->next_result();
$loan_tot_count = (int)$loan_updated_result[0]->tot_count;
if((int)$tot_count > 0 || (int)$loan_tot_count > 0){
$can_process = true;
}else{
$can_process = false;
}
}else{
$can_process = true;
}
//get function name and map column and input value
$statutory_map_qry = 'select map_column,statutory_function_name as function_name,input_column from cw_payroll_function inner join cw_statutory_function on cw_statutory_function.prime_statutory_function_id =cw_payroll_function.function_name where cw_payroll_function.trans_status = 1';
$statutory_map_data = $this->db->query("CALL sp_a_run ('SELECT','$statutory_map_qry')");
$statutory_map_result = $statutory_map_data->result();
$statutory_map_data->next_result();
$statutory_map_list = array();
foreach($statutory_map_result as $statutory_map){
$map_column = $statutory_map->map_column;
$function_name = $statutory_map->function_name;
$input_column = $statutory_map->input_column;
$statutory_map_list[$map_column] = array('map_column'=>$map_column,'function_name'=> $function_name, 'input_column'=>$input_column, 'sts'=>false);
}
$can_process = true;
if($can_process){
// FORMULA LIST
$formula_qry = 'SELECT * FROM cw_payroll_formula where trans_status = 1 and fandf_only = 0 order by abs(formula_order),abs(formula_mode) asc';
$formula_data = $this->db->query("CALL sp_a_run ('SELECT','$formula_qry')");
$formula_result = $formula_data->result();
$formula_data->next_result();
$formula_list = array();
foreach($formula_result as $formula){
$formula_for = $formula->formula_for;
$formula_type = $formula->formula_type;
$out_column = $formula->out_column;
$payroll_formula = $formula->payroll_formula;
$formula_order = $formula->formula_order;
$formula_mode = $formula->formula_mode;
$round_value = $formula->round_value;
$formula_list[$formula_for][$out_column] = array("payroll_formula"=>$payroll_formula,"formula_mode"=>$formula_mode,"round_value"=>$round_value);
}
$formula_code = "\n\t\t\t".' $employee_code = $trans["employee_code"]; $trans["transactions_month"] = $trans["process_month"];';
$count = 0;
$role_array = array();
foreach($formula_list as $role => $formula){
$role_array[] = $role;
$count++;
$sub_formula = "";
$query_key = "";
$query_value = "";
foreach($formula as $key=>$value){
$payroll_formula = $value['payroll_formula'];
$formula_mode = (int)$value['formula_mode'];
$round_value = $value['round_value'];
$preg_match = preg_match_all('#\@(.*?)\@#', $payroll_formula, $match);
foreach($match[1] as $for_rslt){
$find_value = "@$for_rslt@";
$for_value = '$trans["'.$for_rslt.'"]';
$payroll_formula = str_replace($find_value,$for_value,$payroll_formula);
}
$payroll_formula = str_replace("dz(","\$this->dz(",$payroll_formula);
if($round_value){
$payroll_formula = "\$this->rounding_value((".$payroll_formula."),'".$round_value."')";
}else{
$payroll_formula = "$payroll_formula";
}
if(($formula_mode === 1) || ($formula_mode === 2)){
if(array_key_exists($key,$statutory_map_list)){
$function_name = $statutory_map_list[$key][function_name];
$input_column = $statutory_map_list[$key][input_column];
$input_list = explode(",",$input_column);
$input_value = "";
foreach($input_list as $in_key => $value){
$input_value .= "\$trans"."[\"".$value."\"]".",";
}
if(($function_name === "get_pf_value") || ($function_name === "get_esi_value")){
$input_value .= "'$key'";
}
$input_value = rtrim($input_value,',');
$result_function = "\$this->Process_payroll_model->$function_name($input_value)";
$statutory_map_list[$key]['sts'] = true;
}else{
$result_function = $payroll_formula;
}
$sub_formula .= "\n\t\t\t\t".'$trans["'.$key.'"] = '.$result_function.';'."\t\t\t";
}else
if($formula_mode === 3){
$payroll_formula = preg_replace('/\s+/', '', $payroll_formula);
$replace_value = '$trans["'.$key.'"] = ';
$payroll_formula = "\n\t\t\t\t".$payroll_formula;
$sub_formula .= str_replace("return",$replace_value,$payroll_formula);
}
$query_key .= $key.",";
$query_value .= "'".'".$trans["'.$key.'"]."'."',";
}
$query_key = rtrim('employees_id,employee_code,emp_name,transactions_month,role,process_month,termination_status,'.$query_key,",");
$query_value = rtrim($query_value,",");
$function = "";
foreach($statutory_map_list as $fun){
if(!$fun['sts']){
$map_column = $fun['map_column'];
$function_name = $fun['function_name'];
$input_column = $fun['input_column'];
$input_list = explode(",",$input_column);
$input_value = "";
foreach($input_list as $value){
$input_value .= "\$trans"."[\"".$value."\"]".",";
}
$input_value = rtrim($input_value,',');
$result_function = "\$this->Process_payroll_model->$function_name($input_value)";
$function .= "\n\t\t\t\t".'$trans["'.$map_column.'"] = '.$result_function.';'."\t\t\t";
}
}
$value_qry = "'".'".$trans["employees_id"]."'."','".'".$trans["employee_code"]."'."','".'".$trans["emp_name"]."'."','".'".$trans["transactions_month"]."'."','".'".$trans["role"]."'."','".'".$trans["transactions_month"]."'."','".'".$trans["termination_status"]."'."',".$query_value;
//Loan Process START
$loan_column_qry = 'SELECT GROUP_CONCAT(label_name) as loan_columns FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND input_view_type IN (1,2) and loan_check = 1 ORDER BY prime_form_id';
$loan_column_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_column_qry')");
$loan_column_rslt = $loan_column_data->result();
$loan_column_data->next_result();
$loan_columns = array();
if($loan_column_rslt[0]->loan_columns){
$loan_columns = explode(",",$loan_column_rslt[0]->loan_columns);
}
foreach ($loan_columns as $key => $loan_column){
//Generate Query Key
$query_key .= ",".$loan_column."_total";
$query_key .= ",".$loan_column."_installments";
$query_key .= ",".$loan_column."_instal_count";
$query_key .= ",".$loan_column."_balance";
//Generate Query Value
$value_qry .= ",'".'".$trans["'.$loan_column.'_total"]."'."'";
$value_qry .= ",'".'".$trans["'.$loan_column.'_installments"]."'."'";
$value_qry .= ",'".'".$trans["'.$loan_column.'_instal_count"]."'."'";
$value_qry .= ",'".'".$trans["'.$loan_column.'_balance"]."'."'";
}
if($count === 1){
$formula_code .= "\n\t\t\t".'if((int)$trans["role"] === '.$role."){".$function . $sub_formula."\$payroll_array[".$role."][] =\"($value_qry)\";
\n\t\t \$query_key = '".$query_key."'; \n\t \$qry_".$role." = 'INSERT INTO cw_transactions(".$query_key."'.\$trans_key_array.') VALUES '.implode(',',\$payroll_array[".$role."]); \n\t\t\t}";
}else{
$formula_code .= "else\n\t\t\t".'if((int)$trans["role"] === '.$role."){".$sub_formula."\$payroll_array[".$role."][] =\"($value_qry)\";
\n\t\t \$query_key = '".$query_key."'; \n\t \$qry_".$role." = 'INSERT INTO cw_transactions(".$query_key."'.\$trans_key_array.') VALUES '.implode(',',\$payroll_array[".$role."]); \n\t\t\t}";
}
}
$qry_build = "";
//role wise query execution
foreach ($role_array as $key => $role_value){
$qry_build .= "\n\t if(\$payroll_array[".$role_value."]){ \n\t \$this->db->query(\$qry_".$role_value."); \n\t }";
}
$final_code = "\n\t\t".' $payroll_array = array();
foreach($trans_array as $key => $trans){
'.$formula_code."\n\t\t}";
$fname = 'Fandf_calculation($trans_array){';
$final_code = "<?php\n class Fandf_calculation_model extends CI_Model{\n\tpublic function $fname $final_code $qry_build return true; \n\t}\n\n\tpublic function rounding_value(\$result, \$round_mode){\n\t\t\$result = round(\$result,2);\n\t\t\$final_result =0;\n\t\tif((\$round_mode == 0.5) || (\$round_mode == 1)){\n\t\t\t\$final_result = round(\$result/\$round_mode, 0)* \$round_mode;\n\t\t}elseif(\$round_mode == '>1'){\n\t\t\t\$final_result = ceil(\$result);\n\t\t}elseif(\$round_mode == '<1'){\n\t\t\t\$final_result = floor(\$result);\n\t\t}elseif(\$round_mode == '<0.5'){\n\t\t\t\$rslt = explode('.', \$result);\n\t\t\t\$int_value = \$rslt[0];\n\t\t\t\$point_value = \$rslt[1];\n\t\t\tif((int)\$point_value > 50){\n\t\t\t\t\$final_result = \$int_value.'.50';\n\t\t\t}else{\n\t\t\t\t\$final_result = ceil(\$result);\n\t\t\t}\n\t\t}elseif(\$round_mode == '>0.5'){\n\t\t\t\$rslt = explode('.', \$result);\n\t\t\t\$int_value = \$rslt[0];\n\t\t\t\$point_value = \$rslt[1];\n\t\t\tif((int)\$point_value >= 50){\n\t\t\t\t\$final_result = round(\$result);\n\t\t\t}else{\n\t\t\t\t\$final_result = \$int_value.'.50';\n\t\t\t}\n\t\t}elseif(((int)\$round_mode == 5) || ((int)\$round_mode == 10) || ((int)\$round_mode == 50) || ((int)\$round_mode == 100)){\n\t\t\t\$final_result = (ceil(\$result)% \$round_mode === 0) ? ceil(\$result) : round((\$result+ \$round_mode/2)/ \$round_mode)*\$round_mode;\n\t\t}elseif(\$round_mode == 0.1){\n\t\t\t\$final_result = round(\$result, 2);\n\t\t}\n\t\treturn \$final_result;\n\t}
\n\tpublic function dz(\$result){\n\t\tif(is_nan(\$result) || is_infinite(\$result)){\n\t\t\t\$result = 0;\n\t\t}else{\n\t\t\t\$result;\n\t\t}\n\t\treturn \$result;\n\t}\n\n}\n?>";
$formula_temp_file = dirname(__FILE__)."/"."Fandf_calculation_model.php";
$formula_temp_file = str_replace('controllers','models',$formula_temp_file);
$oldmask = umask(0);
fopen("$formula_temp_file", "w");
file_put_contents("$formula_temp_file",$final_code);
chmod($formula_temp_file, 0777);
umask($oldmask);
}
}
//delete the transaction table entry for fandf process
public function fandf_process_delete(){
$employee_code = $this->input->post('employee_code');
$process_month = $this->input->post('process_month');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
if($employee_code){
$upd_trans_query = 'UPDATE cw_transactions SET trans_status = 0, trans_updated_by = "'. $logged_id .'",trans_updated_date = "'.$today_date.'" WHERE employee_code = "'.$employee_code.'" and transactions_month="'.$process_month.'"';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_trans_query')");
$upd_month_query = 'UPDATE cw_monthly_input SET trans_status = 0, trans_updated_by = "'. $logged_id .'",trans_updated_date = "'.$today_date.'" WHERE employee_code = "'.$employee_code.'" and process_month ="'.$process_month.'"';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_month_query')");
$upd_fandf_query = 'UPDATE cw_fandf_process SET trans_status = 0, trans_updated_by = "'. $logged_id .'",trans_updated_date = "'.$today_date.'" WHERE emp_code = "'.$employee_code.'"';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_fandf_query')");
echo json_encode(array('success' => true, 'message' => "Deleted fandf process successfully, run process again!!!"));
}else{
echo json_encode(array('success' => false, 'message' => "Unable to process your request"));
}
}
//total sum
public function total_amt($employee_code,$process_month){
$stop_pay_qry = 'select ifnull(sum(net_pay),0) as stop_pay from cw_employees_stop_payment left join cw_transactions on cw_transactions.employees_id = cw_employees_stop_payment.prime_employees_id where cw_transactions.transactions_month = date_format(cw_employees_stop_payment.stop_pay_month, "%m-%Y") and cw_transactions.employee_code = '.$employee_code.' and cw_employees_stop_payment.stop_payment_type = 1 and cw_transactions.trans_status = 1';
$stop_pay_info = $this->db->query("CALL sp_a_run ('SELECT','$stop_pay_qry')");
$stop_pay_result = $stop_pay_info->result();
$stop_pay_info->next_result();
$stop_pay_amt = $stop_pay_result[0]->stop_pay;
$last_pay_qry = 'select net_pay from cw_transactions where trans_status=1 and employee_code ="'.$employee_code.'" and transactions_month ="'.$process_month.'"';
$last_pay_info = $this->db->query("CALL sp_a_run ('SELECT','$last_pay_qry')");
$last_pay_result = $last_pay_info->result();
$last_pay_info->next_result();
$last_pay = $last_pay_result[0]->net_pay;
$final_settle_amt = $stop_pay_amt + $last_pay;
return $final_settle_amt;
}
//sorting formula updates
public function sort_formula($formula_for,$out_column,$payroll_formula,$formula_mode){
$preg_match_inputs = preg_match_all('#\@(.*?)\@#', $payroll_formula,$preg_match_inputsvalue);
$preg_match_inputsvalue_count = count($preg_match_inputsvalue[1]);
$input_match_column = implode('","',$preg_match_inputsvalue[1]);
$input_match_column ='"'.$input_match_column.'"';
$qu_find_sortorder='select IFNULL(MIN(formula_order), 0) as formula_order_min, IFNULL(MAX(formula_order), 0) as formula_order_max from cw_payroll_formula where formula_for = "'.$formula_for.'" and (out_column="'.$out_column.'" or out_column in ('.$input_match_column.')) and trans_status = 1 order by formula_order desc';
$max_min_data = $this->db->query("CALL sp_a_run ('SELECT','$qu_find_sortorder')");
$max_min_rslt = $max_min_data->result();
$max_min_data->next_result();
//echo $qu_find_sortorder;
//die;
$outcolum_maxorder = $max_min_rslt[0]->formula_order_max;
$outcolum_minorder = $max_min_rslt[0]->formula_order_min;
$outcolum_order = $outcolum_maxorder;
$min = $outcolum_minorder;
$qu_find_sortorder_data='select out_column,formula_order from cw_payroll_formula where formula_for = "'.$formula_for.'" and (formula_mode !=1 or out_column="'.$out_column.'") and formula_order between "'.$min.'" and "'.$outcolum_maxorder.'" and trans_status = 1 order by formula_order asc';
$max_min_sort_data = $this->db->query("CALL sp_a_run ('SELECT','$qu_find_sortorder_data')");
$max_min_sort_rslt = $max_min_sort_data->result();
$max_min_sort_data->next_result();
foreach ($max_min_sort_rslt as $result){
$out_column_db = $result->out_column;
$formula_order = $result->formula_order;
if($out_column==$out_column_db){
if((int)$formula_mode === 1){
$upd_sort = 'UPDATE cw_payroll_formula SET formula_order = 1 where formula_for = "'.$formula_for.'" and out_column = "'.$out_column.'" and trans_status = 1';
}else{
$upd_sort = 'UPDATE cw_payroll_formula SET formula_order = "'.$outcolum_order.'" where formula_for = "'.$formula_for.'" and out_column = "'.$out_column.'" and trans_status = 1';
}
$this->db->query("CALL sp_a_run ('RUN','$upd_sort')");
}else{
$upd_sort = 'UPDATE cw_payroll_formula SET formula_order = "'.$min.'" where formula_for = "'.$formula_for.'" and out_column="'.$out_column_db.'" and formula_order="'.$formula_order.'" and formula_mode !=1 and trans_status = 1';
$this->db->query("CALL sp_a_run ('RUN','$upd_sort')");
$min++;
}
}
if((int)$formula_mode !== 1){
$find_max_order_qry ='select IFNULL(MAX(formula_order), 0) as max_order from cw_payroll_formula where formula_for = "'.$formula_for.'" and trans_status = 1 and out_column != "net_pay" order by formula_order desc';
$max_order_data = $this->db->query("CALL sp_a_run ('SELECT','$find_max_order_qry')");
$max_order_rslt = $max_order_data->result();
$max_order_data->next_result();
$max_order = $max_order_rslt[0]->max_order;
$i = (int)$max_order + 1;
if($max_order){
$upd_sort_net = 'UPDATE cw_payroll_formula SET formula_order = "'.$i.'" where formula_for = "'.$formula_for.'" and out_column = "net_pay"';
$this->db->query("CALL sp_a_run ('RUN','$upd_sort_net')");
}
}
}
//VIEW FUNCTION FOR PAYROLL
public function transaction_data($process_month,$process_emp_code){
$search_val = ' and cw_transactions.employee_code = "'.$process_emp_code.'" ';
$select_query = "";
$pick_query = "";
$final_qry = "";
$thead_line = "";
$thead = "";
$form_qry = 'SELECT prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "employees" and transaction_type in (1,2,3) and (earn_payroll_check = "1" or ded_payroll_check = "1") and trans_status = "1" order by payroll_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
$loan_column_qry = 'SELECT GROUP_CONCAT(label_name) as loan_columns FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND input_view_type IN (1,2) and loan_check = 1 ORDER BY prime_form_id';
$loan_column_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_column_qry')");
$loan_column_rslt = $loan_column_data->result();
$loan_column_data->next_result();
$loan_columns = array();
if($loan_column_rslt[0]->loan_columns){
$loan_columns = explode(",",$loan_column_rslt[0]->loan_columns);
}
$query_key = "";
$loan_array = array();
foreach ($loan_columns as $key => $loan_column){
//Generate Query Key For Loan
$query_key .= ",cw_transactions.".$loan_column."_total";
$query_key .= ",cw_transactions.".$loan_column."_installments";
$query_key .= ",cw_transactions.".$loan_column."_instal_count";
$query_key .= ",cw_transactions.".$loan_column."_balance";
$loan_array[] = $loan_column."_total";
$loan_array[] = $loan_column."_installments";
$loan_array[] = $loan_column."_instal_count";
$loan_array[] = $loan_column."_balance";
}
$loan_thead = "";
foreach($loan_array as $loan){
$loan = str_replace("_", " ", $loan);
$loan = ucwords($loan);
$loan_thead .= "<th>$loan</th>";
}
$table_name = "cw_transactions";
$i = 1;
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
if($label_name == "role"){
$view_name = "Category";
}
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else{
$select_query .= "$table_name.$label_name , ";
}
if((int)$i === 1){
$th_style = "class='hard_left'";
}else
if((int)$i === 2){
$th_style = "class='next_left'";
}else{
$th_style = "";
}
$thead_line .= "<th $th_style>$view_name</th>";
$i++;
}
$thead = "<tr>$thead_line $loan_thead</tr>";
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
$trans_status = " left join cw_employees on cw_employees.employee_code =cw_transactions.employee_code where ".$table_name.".trans_status = 1 and transactions_month=\"".$process_month."\"";
$final_qry = "select $select_query $query_key from $table_name $pick_query $trans_status $search_val";
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
$tr_line = "";
foreach($final_result as $rslt){
$td_line = "";
$j = 1;
foreach($rslt as $value){
if((int)$j === 1){
$cls = "class='hard_left'";
}else
if((int)$j === 2){
$cls = "class='next_left'";
}else{
$cls = "";
}
$td_line .= "<td $cls>$value</td>";
$j++;
}
$tr_line .= "<tr>$td_line</tr>";
}
$table_content = "<div class='outer'><div class='inner' >
<table class='table table-bordered col-style' id='detail_list' style='box-shadow:none;'>
<thead>
$thead
</thead>
<tbody>
$tr_line
</tbody>
</table></div></div>";
return $table_content;
}
}
?>