File: /home/cafsindia/hrms_cafsinfotech_in/OLD/application_bk/controllers/Fandf_process_payroll.php
<?php
/**********************************************************
Filename: Base Controller
Description: Base Controller for all dynamic module controller.
Author: udhayakumar Anandhan
Created on: 12 December 2018
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("Secure_Controller.php");
class Fandf_process_payroll extends Secure_Controller{
public function __construct(){
parent::__construct('fandf_process_payroll');
$this->create_formula_file();
//$this->load->model('Process_payroll_model');
$this->load->model('Hr_methods_model');
$this->load->model('Fandf_calculation_model');
$this->load->model('Formula_model');
$this->load->model('Increment_calculation_model');
$this->load->model('Income_tax_process_model');
$this->load->model('Payroll_calculation_model');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$this->control_name = strtolower($this->router->fetch_class());
$this->logged_id = $this->session->userdata('logged_id');
$this->logged_role = $this->session->userdata('logged_role');
//Get Company information
$company = 'select payroll_process_type from cw_company_information where cw_company_information.trans_status = 1';
$comp_info = $this->db->query("CALL sp_a_run ('SELECT','$company')");
$comp_result = $comp_info->result();
$comp_info->next_result();
$this->payroll_process_type = $comp_result[0]->payroll_process_type;
}
public function index(){
$error_info = $this->validation_page();
if($error_info){
$data['error_info'] = $this->validation_ui($error_info);
// exit(0);
}
//Add Additional Information from the settings
if($this->payroll_process_type){
$get_components = 'select pick_list,pick_list_type,view_name,pick_table,label_name from `cw_form_setting` where prime_module_id = "employees" and input_view_type in (1,2) and field_type = 5 and label_name = "'.$this->payroll_process_type.'"';
$get_components_info = $this->db->query("CALL sp_a_run ('SELECT','$get_components')");
$get_components_result = $get_components_info->result();
$get_components_info->next_result();
$pick_table = $get_components_result[0]->pick_table;
$pick_list = $get_components_result[0]->pick_list;
$pick_list_type = (int)$get_components_result[0]->pick_list_type;
$label_name = $get_components_result[0]->label_name;
$view_name = $get_components_result[0]->view_name;
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($pick_display){
$pick_list = "$pick_list_val_1,CONCAT_WS(\" - \", $pick_display) as $pick_list_val_2";
}
if($pick_table === 'cw_category'){
$pick_query = "select $pick_list from $pick_table where trans_status = 1 and prime_category_id !=1";
}else{
$pick_query = "select $pick_list from $pick_table where trans_status = 1";
}
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
if($pick_result){
$pick_key = array_column($pick_result, $pick_list_val_1);
$pick_val = array_column($pick_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
}
$final_pick = array("" => "---- $label_name ----") + $final_pick;
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query = "select $pick_list_val_1,$pick_list_val_2 from $pick_table where $pick_list_val_3 = 1 ";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
if($pick_result){
$pick_key = array_column($pick_result, $pick_list_val_1);
$pick_val = array_column($pick_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
}
$final_pick = array(" " => "---- $label_name ----") + $final_pick;
}
}
$data['process_role'] = $final_pick;
$data['process_role_lable'] = $view_name;
$data['module_id'] = "transactions";
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "transactions" and trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_format_drop[""] = "---- Excel Format ----";
foreach($excel_result as $excel){
$prime_excel_format_id = $excel->prime_excel_format_id;
$excel_name = $excel->excel_name;
$excel_format_drop[$prime_excel_format_id] = $excel_name;
}
$data['excel_format_drop'] = $excel_format_drop;
$this->load->view("$this->control_name/manage",$data);
}
public function emp_suggest(){
$search_term = $this->input->post_get('term');
$process_mon = $this->input->post_get('process_month');
$final_qry = 'SELECT employee_code,emp_name FROM `cw_monthly_input` WHERE process_month = "'.$process_mon.'" and fandf = "1" and (employee_code like "'.$search_term.'%" or emp_name like "'.$search_term.'%") '; // => [MS 25-07-2024]
$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' => " ", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
//FUNCTION FOR COMMON PAYROLL PROCESS
public function process_payroll(){
$process_month = explode("-",$this->input->post('process_month') ?? "");
$process_type = (int)$this->input->post('process_type');
$process_emp_code = $this->input->post('process_emp_code');
$process_role = implode(",",$this->input->post('process_role') ?? []);
$process_income_tax = $this->input->post('process_income_tax');
$process_proof_wise = $this->input->post('process_proof_wise');
$process_mode = $this->input->post('process_mode');
$process_income_tax_val = 0;
$process_proof_wise_val = 0;
$reprocess_val = 0;
if($process_income_tax === "on"){ $process_income_tax_val = 1; }
if($process_proof_wise === "on"){ $process_proof_wise_val = 1; }
$process_month = $process_month[0]."-".$process_month[1];
//payroll function mapping Check
$function_qry = 'SELECT count(*) as count FROM cw_payroll_function WHERE statutory_name in (3,8) and function_name in (3,5) and trans_status = 1';
$function_info = $this->db->query("CALL sp_a_run ('SELECT','$function_qry')");
$function_rslt = $function_info->result();
$function_info->next_result();
$function_count = $function_rslt[0]->count;
if((int)$function_count < 2){
echo json_encode(array('success' => false, 'message' => 'Please add payroll function for PT Amount and Month Days...'));
exit(0);
}
$fin_query = 'SELECT prime_financial_setting_id,start_date,end_date from cw_financial_setting where set_as_default_financial_year = 1 and trans_status = "1"';
$fin_data = $this->db->query("CALL sp_a_run ('SELECT','$fin_query')");
$fin_result = $fin_data->result();
$fin_data->next_result();
//Get Financial Data
$fin_set_id = $fin_result[0]->prime_financial_setting_id;
$start_date = $fin_result[0]->start_date;
$end_date = $fin_result[0]->end_date;
$process_date = date("Y-m-d",strtotime("01-".$process_month));
//payroll function mapping Check
$pt_amount_qry = 'SELECT count(*) as pt_amount_count FROM cw_payroll_function_map WHERE loc_name in("professional_tax_amount","pt_gross","fixed_pf","arrear_gross") and trans_status = 1';
$pt_amount_info = $this->db->query("CALL sp_a_run ('SELECT','$pt_amount_qry')");
$pt_amount_rslt = $pt_amount_info->result();
$pt_amount_info->next_result();
$pt_amount_count = $pt_amount_rslt[0]->pt_amount_count;
if((int)$pt_amount_count !== 4){
echo json_encode(array('success' => false, 'message' => 'Please add payroll function mapping for PT Amount, Pt Gross,Fixed PF,Arrear Gross and Month Days...'));
exit(0);
}
//Check payroll lock exist
$exist_qry = 'select count(prime_payroll_id) as payroll_count from cw_payroll where pay_month = "'.$process_month.'" and status = 1 and trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$exist_data->next_result();
$num_rows = $exist_result[0]->payroll_count;
if((int)$num_rows > 0){
echo json_encode(array('success' => false, 'message' => 'Payroll Locked for this Month'));
exit(0);
}
$where_query = "";
$cond_query = "";
$where_inc = "";
$where_loan = "";
if($process_type === 1){
$where_query = ' and employee_code = "'.$process_emp_code.'" ';
$cond_query = ' and cw_monthly_input.employee_code = "'.$process_emp_code.'" ';
$where_inc = ' and employee_code = "'.$process_emp_code.'" ';
$where_loan = ' and cw_loan_installment.emp_code = "'.$process_emp_code.'" ';
}else
if($process_type === 2){
$where_query = ' and '.$this->payroll_process_type.' in ('.$process_role.')';
$cond_query = ' and cw_monthly_input.'.$this->payroll_process_type.' in ('.$process_role.')';
//Get Category details for Loan and Increment
$get_role_qry = 'select GROUP_CONCAT(DISTINCT role) as roles from cw_monthly_input where trans_status = 1 and cw_monthly_input.termination_status ="1" and process_month = "'.$process_month.'"'.$where_query;
$get_role_data = $this->db->query("CALL sp_a_run ('SELECT','$get_role_qry')");
$get_role_result = $get_role_data->result();
$get_role_data->next_result();
$process_roles = $get_role_result[0]->roles;
$where_inc = ' and category in ('.$process_roles.')';
$where_loan = ' and cw_loan_installment.category in ('.$process_roles.')';
}
//check monthly data is present or not
// and input_status = 1
$check_monthly_input_qry = 'select count(*) as result_count from cw_monthly_input where trans_status = 1 and input_status = 1 and cw_monthly_input.fandf = "1" and process_month = "'.$process_month.'"'.$where_query;
$check_monthly_data = $this->db->query("CALL sp_a_run ('SELECT','$check_monthly_input_qry')");
$check_monthly_result = $check_monthly_data->result();
$check_monthly_data->next_result();
$check_monthly_count = (int)$check_monthly_result[0]->result_count;
if((int)$check_monthly_count === 0){
echo json_encode(array('success' => false, 'message' => "Monthly Input Not Generated?"));
exit(0);
}
//if check process with income tax(tax process) payroll process and tax process
if(((int)$process_income_tax_val === 1 || (int)$process_proof_wise_val === 1) && ((int)$process_mode === 1)) {
$istrans_exist_qry = 'select count(*) as result_count from cw_transactions where trans_status = 1 and fandf = 1 and transactions_month = "'.$process_month.'" '.$where_query;
$istrans_data = $this->db->query("CALL sp_a_run ('SELECT','$istrans_exist_qry')");
$istrans_result = $istrans_data->result();
$istrans_data->next_result();
$istrans_count = (int)$istrans_result[0]->result_count;
if($istrans_count > 0){
$table_view = $this->transaction_data($process_month,$process_role,$process_emp_code);
echo json_encode(array('success' => false, 'message' => "Already exist in transaction. Please remove transaction!",'table_content' =>$table_view));
}else{//1->process payroll,2->income tax process,3->delete payroll, 4 ->process payroll
$pay_process_function = $this->process_payroll_function($where_query,$cond_query,$where_inc,$where_loan,$process_month,$process_type,$process_emp_code,$process_role,$process_proof_wise);
if($pay_process_function){
$process_mode = 1;
$process_from ="fandf_process_payroll"; //income tax proceess
$tax_process = $this->Income_tax_process_model->income_tax_with_process_payroll($process_month,$annexure_sts,$process_type,$process_emp_code,$process_role,$process_mode,$proof_wise,$month_name,$logged_id,$today_date,$process_from,"");
if($tax_process){
$process_income_tax_val = 0; //delete payroll
$pay_delete_function = $this->payroll_delete_function($where_query,$cond_query,$where_inc,$where_loan,$process_month,$process_emp_code,$process_role,$process_income_tax_val,$process_type);
if($pay_delete_function){
$process_income_tax_val === 0; //reprocss payroll
$pay_process_function = $this->process_payroll_function($where_query,$cond_query,$where_inc,$where_loan,$process_month,$process_type,$process_emp_code,$process_role,$process_proof_wise);
}
}
}
// $payroll_rslt = $this->Fandf_calculation_model->Payroll_calculation($trans_array);
$table_view = $this->transaction_data($process_month,$process_role,$process_emp_code);
$mi_upd_query = 'UPDATE cw_monthly_input SET payroll_status = 1,trans_updated_by = "'.$this->logged_id.'", trans_updated_date = "'.date("Y-m-d H:i:s").'" where trans_status= 1 and input_status = 1 and fandf = 1 '.$where_query;
$this->db->query("CALL sp_a_run ('UPDATE','$mi_upd_query')");
if($table_view){
echo json_encode(array('success' => TRUE,'message' =>"Payroll Processed Successfully!!! ",'table_content' =>$table_view));
exit(0);
}
}
}else
if((int)$process_mode === 1){ //process mode is payroll process without tax process
$istrans_exist_qry = 'select count(*) as result_count from cw_transactions where trans_status = 1 and transactions_month = "'.$process_month.'" and fandf = 1'.$where_query;
$istrans_data = $this->db->query("CALL sp_a_run ('SELECT','$istrans_exist_qry')");
$istrans_result = $istrans_data->result();
$istrans_data->next_result();
$istrans_count = (int)$istrans_result[0]->result_count;
if($istrans_count > 0){ //CHECK DATA IS ALREADY EXIST IN TRANSACTIONS TABLE
$table_view = $this->transaction_data($process_month,$process_role,$process_emp_code);
echo json_encode(array('success' => false, 'message' => "Already exist in transaction. Please remove transaction!",'table_content' =>$table_view));
}else{
$pay_process_function = $this->process_payroll_function($where_query,$cond_query,$where_inc,$where_loan,$process_month,$process_type,$process_emp_code,$process_role,$process_proof_wise);
// return $pay_process_function;
$payroll_rslt = $this->Fandf_calculation_model->Payroll_calculation($trans_array);
$table_view = $this->transaction_data($process_month,$process_role,$process_emp_code);
$mi_upd_query = 'UPDATE cw_monthly_input SET payroll_status = 1,trans_updated_by = "'.$this->logged_id.'", trans_updated_date = "'.date("Y-m-d H:i:s").'" where trans_status= 1 and input_status = 1 and fandf = 1 '.$where_query;
$this->db->query("CALL sp_a_run ('UPDATE','$mi_upd_query')");
if($table_view){
echo json_encode(array('success' => TRUE,'message' =>"Payroll Processed Successfully!!! ",'table_content' =>$table_view));
exit(0);
}
}
}else
if((int)$process_mode === 2){//process mode delete
//DELETE PAYROLL PROCESS
$pay_delete_function = $this->payroll_delete_function($where_query,$cond_query,$where_inc,$where_loan,$process_month,$process_emp_code,$process_role,$process_income_tax_val,$process_type);
if($pay_delete_function){
echo json_encode(array('success' => TRUE,'message' =>"Payroll Process Deleted!!! ",'table_content' =>$table_content));
}
}else
if((int)$process_mode === 3){//PROCESS MODE VIEW FOR SHOW TABLE DATA
$table_view = $this->transaction_data($process_month,$process_role,$process_emp_code);
echo json_encode(array('success' => True, 'message' =>"See payroll details!!! ",'table_content' =>$table_view));
}
}
//FUNCTION FOR PAYROLL PROCESS
public function process_payroll_function($where_query,$cond_query,$where_inc,$where_loan,$process_month,$process_type,$process_emp_code,$process_role,$process_proof_wise){
// EMPLOYEE AND MONTHLY INPUT COLUMN
$column_qry = 'SELECT prime_form_id,GROUP_CONCAT(CASE WHEN earn_month_check = "1" THEN CONCAT("cw_monthly_input.",label_name," as ",label_name) WHEN deduction_month_check="1" THEN CONCAT("cw_monthly_input.",label_name," as ",label_name) ELSE CONCAT("cw_employees.",label_name," as ",label_name) END ORDER BY 1 SEPARATOR ",") AS columns FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND input_view_type IN (1,2) ORDER BY prime_form_id';
$column_data = $this->db->query("CALL sp_a_run ('SELECT','$column_qry')");
$column_rslt = $column_data->result();
$column_data->next_result();
$column_label = $column_rslt[0]->columns;
//Removed by Sathish
//INNER JOIN cw_employees_cf ON cw_employees.prime_employees_id = cw_employees_cf.prime_employees_id
//Loan Process START
$process_dt = date("Y-m-d",strtotime("01-$process_month"));
$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 cw_loan_installment.emp_code,IFNULL(sum(cw_loan_installment.install_amount),0) as paid,cw_loan_installment.loan_type from cw_loan_installment inner join cw_loan on cw_loan.prime_loan_id = cw_loan_installment.loan_id inner join cw_monthly_input on cw_monthly_input.employee_code = cw_loan_installment.emp_code where cw_loan_installment.paid_status = 1 and cw_loan.trans_status=1 and cw_loan_installment.trans_status = 1 and foreclose_sts = 0 '.$where_loan.' and cw_monthly_input.trans_status = 1 and cw_monthly_input.payroll = 1 and cw_monthly_input.fandf = 1 and cw_monthly_input.supplementary_status = 0 and cw_monthly_input.process_month = "'.$process_month.'" group by cw_loan_installment.emp_code,cw_loan_installment.loan_type';
$paid_data = $this->db->query("CALL sp_a_run ('SELECT','$paid_qry')");
$paid_result = $paid_data->result_array();
$paid_data->next_result();
$loan_paid = array_reduce($paid_result, function ($result, $arr) {
$result[$arr['emp_code']][$arr['loan_type']] = $arr;
return $result;
}, array());
//Get Loan Details
$loan_qry = 'SELECT cw_loan_installment.emp_code,cw_loan_installment.loan_type,IFNULL(sum(cw_loan_installment.loan_amount),0) as loan_amount,IFNULL(sum(cw_loan_installment.number_of_installment),0) as number_of_installment,IFNULL(sum(cw_loan_installment.install_amount),0) as install_amount,IFNULL(sum(cw_loan_installment.installment_count),0) as installment_count,cw_loan_installment.category FROM `cw_loan_installment`inner join cw_loan on cw_loan.prime_loan_id = cw_loan_installment.loan_id inner join cw_monthly_input on cw_monthly_input.employee_code = cw_loan_installment.emp_code where install_year = "'.$process_month.'" '.$where_loan.' and cw_monthly_input.trans_status = 1 and cw_monthly_input.payroll = 1 and cw_monthly_input.fandf = 1 and cw_monthly_input.supplementary_status = 0 and cw_loan.trans_status = 1 and cw_loan_installment.trans_status = 1 and foreclose_sts = 0 group by cw_loan_installment.emp_code,cw_loan_installment.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());
$loan_array = array();
$loan_employees = array();
foreach($loan_result as $emp_code => $loan_rslt){
foreach ($loan_columns as $key => $loan_column){
$loan_array[$emp_code][$loan_column."_total"] = $loan_rslt[$loan_column]['loan_amount'];
$loan_array[$emp_code][$loan_column."_installments"] = $loan_rslt[$loan_column]['number_of_installment'];
$loan_array[$emp_code][$loan_column."_instal_count"] = $loan_rslt[$loan_column]['installment_count'];
$loan_array[$emp_code][$loan_column.'_balance'] = 0;
$loan_array[$emp_code][$loan_column] = $loan_rslt[$loan_column]['loan_amount'] - $loan_paid[$emp_code][$loan_column]['paid'];
//+ $loan_rslt[$loan_column]['install_amount']
}
$loan_employees[] = $emp_code;
}
if(count($loan_employees ?? []) > 0){
$imp = '"' . implode( '","', $loan_employees ?? []) . '"';
// FOR GET REMAINING AMOUNT AND UPDATE IN CW_LOAN_INSTALLMENT TABLE [MS 30-08-2024]
$upd_qry_ins_amt = 'UPDATE cw_loan_installment INNER JOIN (SELECT emp_code,loan_id,SUM(install_amount) AS install_amount FROM cw_loan_installment WHERE emp_code IN ('.$imp.') AND trans_status = 1 AND paid_status = 0 AND DATE_FORMAT(STR_TO_DATE(CONCAT(\"01-\",install_year),\"%d-%m-%Y\"),\"%Y-%m-%d\") >= "'.$process_dt.'" GROUP BY emp_code,loan_id) AS li ON cw_loan_installment.emp_code = li.emp_code AND li.loan_id = cw_loan_installment.loan_id SET cw_loan_installment.install_amount = li.install_amount,paid_status = 1,fandf = 1 WHERE cw_loan_installment.emp_code IN ('.$imp.') AND cw_loan_installment.trans_status=1 AND cw_loan_installment.install_year = "'.$process_month.'"';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_qry_ins_amt')");
$upd_query = 'UPDATE cw_loan_installment SET paid_status = 1,fandf = 1,install_amount = 0 where emp_code in ('.$imp.') and trans_status=1 and paid_status = 0 and DATE_FORMAT(STR_TO_DATE(CONCAT(\"01-\",install_year),\"%d-%m-%Y\"),\"%Y-%m-%d\") > "'.$process_dt.'"';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
}
//Loan Process END
//Salary 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';
$month_day_data = $this->db->query("CALL sp_a_run ('SELECT','$month_day_qry')");
$month_day_result = $month_day_data->result_array();
$month_day_data->next_result();
foreach($month_day_result as $arr){
$month_day_result[$arr['category']] = $arr;
}
$emp_info_query = 'select cw_employees.prime_employees_id as employees_id,'.$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_monthly_input.termination_status = 1 and cw_employees.payroll = 1 and cw_employees.role != 1 and cw_monthly_input.trans_status = 1 and cw_monthly_input.supplementary_status = 0 and cw_employees.termination_status=1 and cw_monthly_input.fandf=1 and input_status = 1 and cw_monthly_input.process_month = "'.$process_month.'"'.$cond_query;
$company_info = $this->Module->get_company_info();
$increment_type = $company_info[0]->increment_type;
if((int)$increment_type === 1){
$increment_check_qry = 'SELECT GROUP_CONCAT(DISTINCT(employee_code) ORDER BY employee_code asc) as employee_code from cw_increment inner join cw_increment_approval on cw_increment_approval.prime_increment_approval_id = cw_increment.increment_approval_id where apply_month="'.$process_month.'" '.$where_inc.' and cw_increment_approval.trans_status = 1 and hr_status = 2';
}else
if((int)$increment_type === 2){
$increment_check_qry = 'select GROUP_CONCAT(DISTINCT(employee_code) ORDER BY employee_code asc) as employee_code from cw_increment where apply_on="'.$process_month.'" '.$where_inc.' and trans_status = 1';
}
$increment_check_info = $this->db->query("CALL sp_a_run ('SELECT','$increment_check_qry')");
$increment_check_result = $increment_check_info->result();
$increment_check_info->next_result();
$inc_employee_code = explode(",",$increment_check_result[0]->employee_code ?? "");
if(count($inc_employee_code ?? []) > 0){
if((int)$increment_type === 1){
$inc_columns_qry = 'SELECT DISTINCT(cw_arrear_cumulative.employee_code) as employee_code,cw_arrear_cumulative.effective_date,cw_arrear_cumulative.category,GROUP_CONCAT(DISTINCT(cw_arrear_cumulative.arrear_column) order by arrear_column ASC) as arrear_columns,GROUP_CONCAT(cw_arrear_cumulative.arrear_value order by arrear_column ASC) as arrear_values FROM `cw_arrear_cumulative` inner join cw_increment_approval on cw_increment_approval.prime_increment_approval_id = cw_arrear_cumulative.increment_approval_id where increment_apply_month = "'.$process_month.'" '.$where_inc.' group by employee_code';
}else
if((int)$increment_type === 2){
$inc_columns_qry = 'SELECT DISTINCT(employee_code) as employee_code,effective_date,category,GROUP_CONCAT(arrear_column order by arrear_column ASC) as arrear_columns,GROUP_CONCAT(arrear_value order by arrear_column ASC) as arrear_values FROM `cw_arrear_cumulative` where increment_apply_month = "'.$process_month.'" '.$where_inc.' group by employee_code';
}
$inc_columns_info = $this->db->query("CALL sp_a_run ('SELECT','$inc_columns_qry')");
$inc_columns_result = $inc_columns_info->result();
$inc_columns_info->next_result();
$increment_array = array();
$effective_array = array();
$middle_inc = array();
foreach($inc_columns_result as $key => $inc_rslt){
$category = $inc_rslt->category;
if($month_day_result){
$role = $month_day_result[$category]['category'];
$day_conditions = $month_day_result[$category]['day_conditions'];
$day_count = $month_day_result[$category]['day_count'];
$day_start = $month_day_result[$category]['day_start'];
$day_end = $month_day_result[$category]['day_end'];
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");
if((int)$day_conditions === 2){
$salary_end_date = date("Y-m-t",strtotime($day_end."-".$process_month));
}else{
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}
}
}
$effective_date = $inc_rslt->effective_date;
$arrear_columns = explode(",",$inc_rslt->arrear_columns ?? "");
$arrear_values = explode(",",$inc_rslt->arrear_values ?? "");
$arrear_data = array_combine($arrear_columns,$arrear_values);
$effective_array[$inc_rslt->employee_code] = date("m-Y",strtotime($effective_date));
if(($effective_date < $salary_start_date) || ($effective_date > $salary_start_date && $effective_date < $salary_end_date)){
$increment_array[$inc_rslt->employee_code] = $arrear_data;
}
if($effective_date <= $salary_start_date){
$this->increment_update($inc_rslt->employee_code,$process_month,$inc_rslt->category);
}else{
$middle_inc[] = array('employee_code'=>$inc_rslt->employee_code,'category'=>$inc_rslt->category);
}
}
}
//ESI Status Update
$this->update_esi_status($where_query,$process_month,$middle_inc);
//Get Increment mapping Columns
//$mapping_data = $this->Hr_methods_model->get_mapped_db_column(12);
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_info_query')");
$emp_rslt = $emp_data->result_array();
$emp_data->next_result();
$trans_array = array();
foreach($emp_rslt as $rslt){
$prime_employees_id = (int)$rslt['prime_employees_id'];
$employee_code = $rslt['employee_code'];
$role = (int)$rslt['role'];
$tax_location = (int)$rslt['professional_tax_location'];
$emp_age = (int)$rslt['emp_age'];
$trans_array[$employee_code] = $rslt;
}
//Loan Data Array Merge
$trans_array = array_replace_recursive($trans_array, $loan_array);
//Increment Data Array Merge
$trans_array = array_replace_recursive($trans_array, $increment_array);
//Middle Increment Update
if(!empty($middle_inc)){
foreach($middle_inc as $middle){
$category = $middle['category'];
$emp_code = $middle['employee_code'];
$this->increment_update($emp_code,$process_month,$category);
}
}
$payroll_rslt = $this->Fandf_calculation_model->Payroll_calculation($trans_array);
if($payroll_rslt){
$mi_upd_query = 'UPDATE cw_monthly_input SET payroll_status = 1,trans_updated_by = "'.$this->logged_id.'", trans_updated_date = "'.date("Y-m-d H:i:s").'" where trans_status= 1 and payroll = 1 and input_status = 1 and fandf = 1 '.$where_query;
$upd_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$mi_upd_query')");
if($upd_rslt){
return $payroll_rslt;
}
}
}
//FUNCTION FOR PAYROLL DELETE
public function payroll_delete_function($where_query,$cond_query,$where_inc,$where_loan,$process_month,$process_emp_code,$process_role,$process_income_tax_val,$process_type){
$logged_id = $this->session->userdata('logged_id');
$date = "01-".$process_month;
$today_date = date("Y-m-d h:i:s");
$get_month_yrs = date('m-Y', strtotime($date. ' + 1 months'));
$last_dlt_qry = 'SELECT count(*) as dlt_count FROM cw_transactions WHERE trans_status = 1 and transactions_month = "' . $get_month_yrs . '" and fandf = 1 ' . $where_query;
$last_dlt_info = $this->db->query("CALL sp_a_run ('SELECT','$last_dlt_qry')");
$last_dlt_rslt = $last_dlt_info->result();
$last_dlt_info->next_result();
$dlt_count = $last_dlt_rslt[0]->dlt_count;
if((int)$dlt_count > 0){
echo json_encode(array('success' => FALSE,'message' =>"Next Month Payroll Already Processed!!!"));
exit();
}
//tax delete process
if((int)$process_income_tax_val === 1){
if($process_type === 1){
$del_query = 'and emp_code = "' . $process_emp_code . '"';
}else if($process_type === 2){
$del_query = 'and emp_role = "' . $process_role . '"';
}
$delete_ins_query = 'INSERT INTO cw_tax_calculation_dlt SELECT cw_tax_calculation.*,"' . $this->logged_id . '" as trans_deleted_by,"'.$today_date.'" as trans_deleted_date FROM cw_tax_calculation inner join cw_transactions on cw_transactions.employee_code = cw_tax_calculation.emp_code WHERE cw_transactions.trans_status = 1 and cw_transactions.fandf=1 and cw_transactions.termination_status =1 and cw_tax_calculation.process_month = "' . $process_month . '"' . $del_query;
$save_result = $this->db->query("CALL sp_a_run ('RUN','$delete_ins_query')");
$prime_dlt_data = 'trans_deleted_by = "'. $this->logged_id .'",trans_deleted_date = "'.$today_date.'"';
$prime_dlt_query = 'UPDATE cw_tax_calculation_dlt SET '. $prime_dlt_data .' WHERE trans_status = 1 and process_month = "' . $process_month . '"' . $del_query;
$this->db->query("CALL sp_a_run ('UPDATE','$prime_dlt_query')");
if($save_result){
$tax_delete_query = 'DELETE cw_tax_calculation from cw_tax_calculation inner join cw_transactions on cw_transactions.employee_code = cw_tax_calculation.emp_code WHERE cw_transactions.trans_status = 1 and cw_transactions.fandf = 1 and cw_transactions.termination_status =1 and cw_tax_calculation.process_month = "' . $process_month . '"' . $del_query;
$this->db->query("CALL sp_a_run ('RUN','$tax_delete_query')");
$num_deleted_rows = $this->db->affected_rows();
}
}
//Loan Update paid Status
$process_dt = date("Y-m-d",strtotime("01-$process_month"));
$upd_query = 'UPDATE cw_loan_installment inner join cw_monthly_input on cw_monthly_input.employee_code = cw_loan_installment.emp_code inner join cw_loan on cw_loan.emp_code = cw_loan_installment.emp_code SET paid_status = 0 where date_format(str_to_date(CONCAT("01-", install_year), "%d-%m-%Y") , "%Y-%m-%d") >= "'.$process_dt.'" and cw_monthly_input.fandf = 1 and cw_loan_installment.trans_status = 1 and cw_loan.foreclose_sts = 0 and cw_loan_installment.fandf=1'.$where_loan;
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
$process_payroll_query = 'INSERT INTO cw_transactions_dlt SELECT * FROM cw_transactions WHERE trans_status = 1 and fandf = 1 and transactions_month = "' . $process_month . '" ' . $where_query;
$save_result = $this->db->query("CALL sp_a_run ('RUN','$process_payroll_query')");
if($save_result){
$process_payroll_delete_query = 'DELETE from cw_transactions WHERE trans_status = 1 and fandf = 1 and transactions_month = "' . $process_month . '" ' . $where_query;
$this->db->query("CALL sp_a_run ('RUN','$process_payroll_delete_query')");
$process_payroll_upd_query = 'UPDATE cw_transactions_dlt SET trans_deleted_by = "' . $logged_id . '",trans_deleted_date = DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%S") WHERE trans_status = 1 and process_month = "' . $process_month . '"' . $where_query;
$this->db->query("CALL sp_a_run ('RUN','$process_payroll_upd_query')");
}
//update mi
$mi_upd_query = 'UPDATE cw_monthly_input SET payroll_status = 0,trans_updated_by = "'.$this->logged_id.'", trans_updated_date = "'.date("Y-m-d H:i:s").'" where trans_status = 1 and fandf = 1 and payroll = 1 and process_month = "' . $process_month . '" '.$where_query;
$this->db->query("CALL sp_a_run ('UPDATE','$mi_upd_query')");
$table_view = $this->transaction_data($process_month,$process_role,$process_emp_code);
$table_view_rslt = json_decode($table_view);
$table_content = $table_view_rslt->table_content;
if((int)$fandf_check_count === 1){
$fandf_update_qry = 'update cw_fandf_process SET trans_status = 0, trans_deleted_by = "'.$logged_id.'",trans_deleted_date = DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%S") where emp_code = "'.$process_emp_code.'"';
$this->db->query("CALL sp_a_run ('RUN','$fandf_update_qry')");
}
$inc_columns_qry = 'SELECT employee_code as employee_code,column_name,current_value FROM `cw_increment` where apply_on = "'.$process_month.'" '.$where_inc.' GROUP BY employee_code,column_name';
$inc_columns_info = $this->db->query("CALL sp_a_run ('SELECT','$inc_columns_qry')");
$inc_columns_result = $inc_columns_info->result_array();
$inc_columns_info->next_result();
foreach($inc_columns_result as $arr){
$increment_array[$arr['employee_code']][$arr['column_name']] = $arr['current_value'];
}
if(!empty($increment_array)){
foreach($increment_array as $emp_code => $arrear_data){
$inc_upd_qry = "";
if(!empty($arrear_data)){
foreach($arrear_data as $column_name => $current_value){
if($column_name)
$inc_upd_qry .= $column_name.'="'.$current_value.'",';
}
if($inc_upd_qry){
$inc_upd_qry .= 'trans_updated_by = "'.$logged_id.'", trans_updated_date = "'.$update_date.'"';
$upd_inc_query = 'UPDATE cw_employees SET '.$inc_upd_qry.' WHERE employee_code = "'.$emp_code.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_inc_query')");
}
}
}
}
return true;
}
//CREATE_FORMULA_FILE
public function create_formula_file(){ //file path with server
$filename = dirname(__FILE__)."/"."Fandf_calculation_model.php";
$filename = str_replace('controllers','models',$filename);
$earning_col = "";
$deduction_col = "";
$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;
$earn_ded_updated_qry = 'SELECT count(*) as earn_ded_count FROM cw_form_setting WHERE prime_module_id = "employees" AND cw_form_setting.trans_status = 1 and (gross_check = 1 or deduction_check =1) and trans_created_date >= "'.$created_date.'" or trans_updated_date >= "'.$created_date.'"';
$earn_ded_updated_info = $this->db->query("CALL sp_a_run ('SELECT','$earn_ded_updated_qry')");
$earn_ded_updated_rslt = $earn_ded_updated_info->result();
$earn_ded_updated_info->next_result();
$earn_ded_count = (int)$earn_ded_updated_rslt[0]->earn_ded_count;
if((int)$earn_ded_count > 0){
$can_process = true;
}
}
}else{
$can_process = true;
}
//$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);
}
if($can_process){
// $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);
// }
//Total Earnings and Total Deduction Auto Calculate
$earn_ded_qry = 'SELECT CASE WHEN gross_check = 1 THEN GROUP_CONCAT("$trans[""",label_name,"""]+") END as total_earnings,CASE WHEN deduction_check = 1 THEN GROUP_CONCAT("$trans[""",label_name,"""]+") END as total_deductions,input_for FROM cw_form_setting WHERE prime_module_id = "employees" AND cw_form_setting.trans_status = 1 and (gross_check = 1 or deduction_check =1) group by input_for order by input_for';
$earn_ded_info = $this->db->query("CALL sp_a_run ('SELECT','$earn_ded_qry')");
$earn_ded_rslt = $earn_ded_info->result_array();
$earn_ded_info->next_result();
$earning_col = $earn_ded_rslt[0]['total_earnings'];
$earning_col = rtrim(implode('',explode(',', $earning_col ?? "") ?? []),'+');
$deduction_col = $earn_ded_rslt[1]['total_deductions'];
$deduction_col = rtrim(implode('',explode(',', $deduction_col ?? "") ?? []),'+');
//END Total Earnings and Total Deduction Auto Calculate
// FORMULA LIST
$formula_qry = 'SELECT * FROM cw_payroll_formula where trans_status = 1 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(strpos($payroll_formula,"/") !== false){
if($this->config->item("db_name") === 'pothys_hrms_db' || $this->config->item("db_name") === 'rebar_hrms_db'){ // Need to check later
$payroll_formula = str_replace(" (","\$this->dz(",$payroll_formula);
}else{
$payroll_formula = str_replace("(","\$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->$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.'"]."'."',";
}
if($earning_col){
$sub_formula .= "\n\t\t\t\t".'$trans["total_earnings"] = '.'$this->rounding_value(('.$earning_col.'),\'1\');'."\t\t\t";
}
if($deduction_col){
$sub_formula .= "\n\t\t\t\t".'$trans["total_deductions"] = '.'$this->rounding_value(('.$deduction_col.'),\'1\');'."\t\t\t";
}
if($earning_col && $deduction_col){
$sub_formula .= "\n\t\t\t\t".'$trans["net_pay"] = '.'$this->rounding_value(($trans["total_earnings"]-$trans["total_deductions"]),\'1\');'."\t\t\t";
}
$query_key = rtrim('employees_id,transactions_month,total_earnings,total_deductions,net_pay,trans_created_by,trans_created_date,settlement_date,fandf,'.$query_key,",");
$query_value = rtrim('\"$logged_id\",\"$date\",\"$settlement_date\",\"1\",'.$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->$function_name($input_value)";
$function .= "\n\t\t\t\t".'$trans["'.$map_column.'"] = '.$result_function.';'."\t\t\t";
}
}
$value_qry = "'".'".$trans["employees_id"]."'."','".'".$trans["transactions_month"]."'."','".'".$trans["total_earnings"]."'."','".'".$trans["total_deductions"]."'."','".'".$trans["net_pay"]."'."',".$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";
//$query_key .= ",".$loan_column;
//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"]."'."'";
//$value_qry .= ",'".'".$trans["'.$loan_column.'"]."'."'";
}
$exist_keys = str_replace("employees_id,","",$query_key);
//Basic Transaction Informations added automatically from employees
$basic_column_qry = 'SELECT GROUP_CONCAT(label_name) as query_key FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND transaction_type = 1 ORDER BY prime_form_id';
$basic_column_data = $this->db->query("CALL sp_a_run ('SELECT','$basic_column_qry')");
$basic_column_rslt = $basic_column_data->result();
$basic_column_data->next_result();
$basic_columns = array();
if($basic_column_rslt[0]->query_key){
$query_key .= ",".$basic_column_rslt[0]->query_key;
$basic_columns_arr = explode(",",$basic_column_rslt[0]->query_key ?? "");
}
foreach ($basic_columns_arr as $key => $basic_column){
//Generate Query Value
$value_qry .= ",'".'".$trans["'.$basic_column.'"]."'."'";
}
//\$query_key = '".$query_key."';
if($count === 1){
$formula_code .= "\n\t\t\t".'if((int)$trans["role"] === '.$role."){".$function . $sub_formula."\n\t \$payroll_array[".$role."][] =\"($value_qry)\";
\n\t\t \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." \n\t \$payroll_array[".$role."][] =\"($value_qry)\";
\n\t\t \n\t \$qry_".$role." = 'INSERT INTO cw_transactions(".$query_key."'.\$trans_key_array.') VALUES '.implode(',',\$payroll_array[".$role."]); \n\t\t\t}";
}
}
$qry_build = "";
$final_qry_build = "";
//role wise query execution
$k = 1;
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_qry_build .= "\$this->db->query(\$qry_".$role_value.")";
if($k !== count($role_array)){
$final_qry_build .= " && ";
}*/
$k++;
}
//$formula_code .= "\n\t\t\t".'$trans_array[$key] = $trans;';
$final_code = "\n\t\t".' $payroll_array = array();
foreach($trans_array as $key => $trans){
'.$formula_code."\n\t\t}";
//\$this->db->query($qry);
$logged_id = "\n\t\t".'$logged_id = $this->session->userdata("logged_id");';
$date = "\n\t\t".'$date = date("Y-m-d H:i:s");';
$settlement_date = "\n\t\t".'$settlement_date = date("Y-m-d");';
$fname = 'Payroll_calculation($trans_array){';
$final_code = "<?php\n class Fandf_calculation_model extends CI_Model{
\n\tprivate \$month_day_res;
\n\tprivate \$sup_emp;
\n\tprivate \$ptax_rslt;
\n\tprivate \$statutory_arr;
\n\tprivate \$get_map_arr;
\n\tprivate \$lwf_emp_arr;
\n\tprivate \$emp_date_arr;
\n\tprivate \$gratuity_arr;
\n\tprivate \$ptax_arr;
\n\tpublic function $fname
\$month_day_qry = 'SELECT category,day_conditions,day_count,day_start,day_end from cw_month_day where cw_month_day.trans_status = 1 ';
\$month_day_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$month_day_qry')\");
\$month_day_result = \$month_day_data->result_array();
\$month_day_data->next_result();
\$this->month_day_res = array_reduce(\$month_day_result, function (\$result, \$arr) {
\$result[\$arr['category']] = \$arr;
return \$result;
}, array()); \n\t
//get tax settings info
\$ptax_qry = 'select cw_professional_tax.prime_professional_tax_id,location,calculation_period,ptax_deduction_month_first as first_period,ptax_deduction_month_second as second_period, osm_first_end,osm_second_end,osm_first_start,osm_second_start from cw_professional_tax where cw_professional_tax.trans_status = 1';
\$ptax_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$ptax_qry')\");
\$ptax_result = \$ptax_data->result_array();
\$ptax_data->next_result();
\$this->ptax_rslt = array_reduce(\$ptax_result, function (\$result, \$arr) {
\$result[\$arr['location']] = \$arr;
return \$result;
}, array()); \n\t
// get_document_fees
/*\$payroll_exist_query = 'SELECT employee_code,count(employee_code) as pay_count from cw_transactions where termination_status = 0 and trans_status = 1 group by employee_code';
\$payroll_exist_info = \$this->db->query(\"CALL sp_a_run ('RUN','\$payroll_exist_query')\");
\$payroll_exist_result = \$payroll_exist_info->result_array();
\$payroll_exist_info->next_result();*/
//get statutory array
\$get_statutory_qry = 'SELECT * FROM cw_statutory WHERE trans_status = 1';
\$get_statutory_info = \$this->db->query(\"CALL sp_a_run ('SELECT','\$get_statutory_qry')\");
\$get_statutory_rslt = \$get_statutory_info->result_array();
\$get_statutory_info->next_result();
\$this->statutory_arr = array_reduce(\$get_statutory_rslt, function (\$result, \$arr) {
\$result[\$arr['category']] = \$arr;
return \$result;
}, array()); \n\t
//get Payroll Function Map array
\$get_map_qry = 'select * from cw_payroll_function_map where trans_status=1';
\$get_map_info = \$this->db->query(\"CALL sp_a_run ('SELECT','\$get_map_qry')\");
\$get_map_rslt = \$get_map_info->result_array();
\$get_map_info->next_result();
\$this->get_map_arr = array_reduce(\$get_map_rslt, function (\$result, \$arr) {
\$result[\$arr['loc_name']] = \$arr;
return \$result;
}, array()); \n\t
//Get lwf_employee & lwf_employer
\$select_lwf_qry = 'select pay_month,company_amount,employee_amount from cw_lwf_setting inner join cw_lwf_setting_lwf_pay_month on cw_lwf_setting_lwf_pay_month.prime_lwf_setting_id=cw_lwf_setting.prime_lwf_setting_id WHERE cw_lwf_setting.trans_status =1';
\$select_lwf_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$select_lwf_qry')\");
\$select_lwf_result = \$select_lwf_data->result_array();
\$select_lwf_data->next_result();
\$this->lwf_emp_arr = array_reduce(\$select_lwf_result, function (\$result, \$arr) {
\$result[\$arr['lwf_location']] = \$arr;
return \$result;
}, array()); \n\t
//Get Employee Date
\$emp_date_qry = 'select last_working_date,date_of_joining,employee_code,termination_status,esi_location,esi_eligibility from cw_employees where trans_status = 1';
\$emp_date_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$emp_date_qry')\");
\$emp_date_rslt = \$emp_date_data->result_array();
\$emp_date_data->next_result();
\n\t
foreach(\$emp_date_rslt as \$key => \$value){
\$this->emp_date_arr[\$value['employee_code']] = \$value;
} \n\t
/*\$this->emp_date_arr = array_reduce(\$emp_date_rslt, function (\$result, \$arr) {
\$result[\$arr['employee_code']] = \$arr;
return \$result;
}, array()); \n\t*/
//Get Gratuity
\$check_eligibilty_qry = 'select category,working_days,number_of_years,pay_days,year_rounding,formula_detail,formula_rounding from cw_gratuity where trans_status = 1';
\$check_eligibilty_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$check_eligibilty_qry')\");
\$check_eligibilty_result = \$check_eligibilty_data->result_array();
\$check_eligibilty_data->next_result();
\$this->gratuity_arr = array_reduce(\$check_eligibilty_result, function (\$result, \$arr) {
\$result[\$arr['category']] = \$arr;
return \$result;
}, array()); \n\t
//Get Gratuity
\$ptax_arr_qry = 'select cw_professional_tax.prime_professional_tax_id,location,calculation_period,ptax_deduction_month_first as first_period,ptax_deduction_month_second as second_period, osm_first_end,osm_second_end,osm_first_start,osm_second_start from cw_professional_tax_tax_range inner join cw_professional_tax on cw_professional_tax.prime_professional_tax_id = cw_professional_tax_tax_range.prime_professional_tax_id where cw_professional_tax.trans_status = 1';
\$ptax_arr_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$ptax_arr_qry')\");
\$ptax_arr_result = \$ptax_arr_data->result_array();
\$ptax_arr_data->next_result();
\n\t
foreach(\$emp_date_rslt as \$key => \$value){
\$this->ptax_arr[\$value['location']] = \$value;
} \n\t
/*\$this->ptax_arr = array_reduce(\$ptax_arr_result, function (\$result, \$arr) {
\$result[\$arr['location']] = \$arr;
return \$result;
}, array()); \n\t*/
\$emp_qry = 'select GROUP_CONCAT(employee_code) as sup_emp from cw_monthly_input where trans_status = 1 and supplementary_status = 1';
\$emp_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$emp_qry')\");
\$emp_result = \$emp_data->result();
\$emp_data->next_result();
\$this->sup_emp = \$emp_result[0]->sup_emp;
\n\t $logged_id $date $settlement_date $final_code $qry_build \n\t 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} //GET TOTAL WORKING DAYS
public function get_total_work_days(\$role,\$process_month){
\$month_day_result = \$this->month_day_res[\$role];
if(\$month_day_result){
\$role = \$month_day_result['category'];
\$day_conditions = \$month_day_result['day_conditions'];
\$day_count = \$month_day_result['day_count'];
\$day_start = \$month_day_result['day_start'];
\$day_end = \$month_day_result['day_end'];
\$month_day = array();
if((int)\$day_conditions === 1){
//STATIC DAYS
\$month_day = \$day_count;
}else
if((int)\$day_conditions === 2){
//CALENDER DAYS
\$trans_month = explode('-',\$process_month);
\$month_val = \$trans_month[0];
\$year_val = \$trans_month[1];
\$day_count = cal_days_in_month(CAL_GREGORIAN, \$month_val, \$year_val);
\$month_day = \$day_count;
}else
if((int)\$day_conditions === 3){
//CUTOFF DAYS
\$trans_month = explode('-',\$process_month);
\$month_val = \$trans_month[0];
\$year_val = \$trans_month[1];
\$pre_month = (int)\$month_val - 1;
\$pre_year_val = \$year_val;
if((int)\$pre_month === 0){
\$pre_month = 12;
\$pre_year_val = (int)\$year_val - 1;
}
\$start_date = \$day_start.\"-\".\$pre_month.\"-\".\$pre_year_val;
\$end_date = \$day_end.\"-\".\$month_val.\"-\".\$year_val;
\$start_date = strtotime(\$start_date);
\$end_date = strtotime(\$end_date);
\$datediff = \$end_date - \$start_date;
\$day_count = round(\$datediff/86400);//60 * 60 * 24
\$month_day = (int)\$day_count + 1;
}
}else{
\$trans_month = explode(\"-\",\$process_month);
\$month_val = \$trans_month[0];
\$year_val = \$trans_month[1];
\$day_count = cal_days_in_month(CAL_GREGORIAN, \$month_val, \$year_val);
\$month_day = \$day_count;
}
return \$month_day;
}
//GET DIFFERENTIAL DAY COUNT FOR NEW JOINING details
//intermediate joining date and month days count differentiate day count
public function get_differential_day(\$employee_code,\$role,\$process_month){
\$month_day_result = \$this->month_day_res[\$role];
\$tot_month_day = \$this->get_total_work_days(\$role,\$process_month);
if(\$month_day_result){
\$day_conditions = \$month_day_result['day_conditions'];
\$day_count = \$month_day_result['day_count'];
\$day_start = \$month_day_result['day_start'];
\$day_end = \$month_day_result['day_end'];
\$diff_day_qry = 'select date_of_joining from cw_employees where trans_status = 1 and employee_code = \"'.\$employee_code.'\" and DATE_FORMAT(date_of_joining, \"%m-%Y\") like \"'.\$process_month.'\"';
\$diff_day_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$diff_day_qry')\");
\$diff_day_result = \$diff_day_data->result();
\$diff_day_data->next_result();
\$doj = \$diff_day_result[0]->date_of_joining;
\$diff_day_count = \$diff_day_data->num_rows();
if(((int)\$day_conditions === 1) || ((int)\$day_conditions === 2)){//STATIC DAYS AND CALENDER DAYS
if((int)\$diff_day_count === 0){
\$final_dif_day = 0;
}else{
\$trans_month = explode('-',\$process_month);
\$month_val = \$trans_month[0];
\$year_val = \$trans_month[1];
if((int)\$day_conditions === 2){
\$day_count = cal_days_in_month(CAL_GREGORIAN, \$month_val, \$year_val);
}
\$process_date = \$day_count.'-'.\$process_month;
\$process_date = date('d-m-Y', strtotime(\$process_date));
\$process_date = strtotime(\$process_date);
\$doj = strtotime(\$doj);
\$diff = \$process_date - \$doj;
\$differ_day = round(\$diff / 86400);
\$final_dif_day = \$tot_month_day - \$differ_day - 1;
}
}else
if((int)\$day_conditions === 3){//CUTOFF DAYS
\$trans_month = explode(\"-\",\$process_month);
\$month_val = \$trans_month[0];
\$year_val = \$trans_month[1];
\$process_end = \$day_end.\"-\".\$month_val.\"-\".\$year_val;
\$process_end = date('Y-m-d',strtotime(\$process_end));
\$pre_month = (\$month_val - 1) % 12;
\$process_start = \$day_start.\"-\".\$pre_month.\"-\".\$year_val;
\$process_start = date('Y-m-d',strtotime(\$process_start));
\$diff_cut_day_qry = 'select date_of_joining from cw_employees where trans_status = 1 and employee_code = \"'.\$employee_code.'\" and DATE_FORMAT(date_of_joining, \"%Y-%m-%d\") between \"'.\$process_start.'\" and \"'.\$process_end.'\"';
\$diff_cut_day_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$diff_cut_day_qry')\");
\$diff_cut_day_result = \$diff_cut_day_data->result();
\$diff_cut_day_data->next_result();
\$diff_cut_day_count = \$diff_cut_day_data->num_rows();
\$doj = \$diff_cut_day_result[0]->date_of_joining;
if((int)\$diff_cut_day_count === 0){
\$final_dif_day = 0;
}else{
\$doj = strtotime(\$doj);
\$process_end = strtotime(\$process_end);
\$diff = \$process_end - \$doj;
\$differ_day = round(\$diff / 86400);
\$final_dif_day = \$tot_month_day - \$differ_day - 1;
}
}
}
return \$final_dif_day;
}
\n\t//GET TOTAL LOAN AMOUNT FOR PER EMPLOYEE
\n\tpublic function get_loan_value(\$employee_code,\$process_month){
\$get_terminate_qry = 'select termination_status from cw_employees where trans_status=1 and employee_code =\"'.\$employee_code.'\"';
\$get_terminate_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$get_terminate_qry')\");
\$get_terminate_result = \$get_terminate_data->result();
\$get_terminate_data->next_result();
\$terminate_sts = \$get_terminate_result[0]->termination_status;
\$loan_amt = 0;
if((int)\$terminate_sts === 1){
\$loan_qry = 'select IFNULL(sum(install_amount),0) as install_amt from cw_loan_installment where trans_status = 1 and paid_status = 0 and emp_code =\"'.\$employee_code.'\" and date_format(str_to_date(install_year, \"%m-%Y\") , \"%Y-%m\") >= date_format(str_to_date(\"'.\$process_month.'\", \"%m-%Y\"), \"%Y-%m\")';
\$loan_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$loan_qry')\");
\$loan_result = \$loan_data->result();
\$loan_data->next_result();
if(!empty(\$loan_result)){
\$loan_amt = \$loan_result[0]->install_amt;
}
}else{
\$loan_qry = 'select IFNULL(install_amount,0) as install_amt from cw_loan_installment where trans_status = 1 and paid_status = 0 and emp_code =\"'.\$employee_code.'\" and install_year =\"'.\$process_month.'\"';
\$loan_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$loan_qry')\");
\$loan_result = \$loan_data->result();
\$loan_data->next_result();
\$loan_amt = 0;
if(!empty(\$loan_result)){
\$loan_amt = \$loan_result[0]->install_amt;
if(\$loan_amt > 0){
\$upd_query = 'UPDATE cw_loan_installment SET paid_status = 1 WHERE trans_status = 1 and install_year =\"'.\$process_month.'\" and emp_code=\"'.\$employee_code.'\"';
\$update_info = \$this->db->query(\"CALL sp_a_run ('UPDATE','\$upd_query')\");
}
}
}
return \$loan_amt;
}
//Get ESI STATUS 20SEP2022 BSK
public function get_esi_status(\$earned_gross,\$employee_code,\$role,\$process_month){
\$statutory_rslt = \$this->statutory_arr[\$role];
\$esi_limit = \$statutory_rslt['esi_limit'];
\$esi_conditions = \$statutory_rslt['esi_conditions'];
\$esi_start_check_month = \$statutory_rslt['esi_start_check_month'];
\$esi_end_check_month = \$statutory_rslt['esi_end_check_month'];
\$process_month = \$statutory_rslt['process_month'];
\$process_month_val = \$statutory_rslt['process_month_val'];
\$esi_loc_db = \$this->get_map_arr['esi_loc']['db_column'];
\$esi_elig_db = \$this->get_map_arr['esi_elig']['db_column'];
if(\$esi_limit > 0){
//location nil is default
//1 -- Nil default no esi sts 2
/*\$select_loc_qry = 'select '.\$esi_loc_db.' as esi_loc from cw_employees where trans_status = 1 and role = \"'.\$role.'\" and employee_code = \"'.\$employee_code.'\"';
\$select_loc_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$select_loc_qry')\");
\$select_loc_result = \$select_loc_data->result_array();
\$select_loc_data->next_result();*/
\$esi_location = \$this->emp_date_arr[\$employee_code][\$esi_loc_db];
\$esi_eligibility = \$this->emp_date_arr[\$employee_code][\$esi_elig_db];
if((int)\$esi_location){
\$upd_query = 'UPDATE cw_employees SET esi_eligibility = 2 WHERE trans_status = 1 and role =\"'.\$role.'\" and employee_code=\"'.\$employee_code.'\"';
\$update_info = \$this->db->query(\"CALL sp_a_run ('UPDATE','\$upd_query')\");
\$esi_sts = 2;
return \$esi_sts;
}else{
//esi eligibility 1= yes 2 = no;
if((\$esi_start_check_month === \$process_month_val) || (\$esi_end_check_month === \$process_month_val)){
if((int)\$earned_gross > (int)\$esi_limit){
\$upd_query = 'UPDATE cw_employees SET esi_eligibility = 2 WHERE trans_status = 1 and role =\"'.\$role.'\" and employee_code=\"'.\$employee_code.'\"';
\$update_info = \$this->db->query(\"CALL sp_a_run ('UPDATE','\$upd_query')\");
\$esi_sts = 2;
return \$esi_sts;
}else{
\$esi_sts = 1; //yes esi
return \$esi_sts;
}
}else{
\$select_esi_elig_qry = 'select '.\$esi_loc_db.' as esi_elig from cw_employees where trans_status = 1 and role = \"'.\$role.'\" and employee_code = \"'.\$employee_code.'\"';
\$select_esi_elig_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$select_esi_elig_qry')\");
\$select_esi_elig_result = \$select_esi_elig_data->result();
\$select_esi_elig_data->next_result();
\$esi_sts = \$select_esi_elig_result[0]->esi_elig;
return \$esi_sts; //actual esi status from DB//
}
}
}
}
//GET LWF EMPLOYER (COMPANY AMOUNT) FUNCTION
public function get_lwf_employer(\$lwf_location,\$process_month){
\$process_month = explode(\"-\",\$process_month);
\$process_month_val = \$process_month[0];
//\$process_month_val = 12;
\$select_lwf_result = \$this->lwf_emp_arr[\$lwf_location];
\$pay_month = \$select_lwf_result['pay_month'];
\$company_amount = \$select_lwf_result['company_amount'];
\$lwf_comp_amt = 0;
if(\$pay_month == \"process_month_val\"){
\$lwf_comp_amt = \$company_amount;
}
return \$lwf_comp_amt;
}
//GET LWF EMPLOYEE (EMPLOYEE AMOUNT) FUNCTION
public function get_lwf_employee(\$lwf_location,\$process_month){
\$process_month = explode(\"-\",\$process_month);
\$process_month_val = \$process_month[0];
\$select_lwf_result = \$this->lwf_emp_arr[\$lwf_location];
\$pay_month = \$select_lwf_result['pay_month'];
\$employee_amount = \$select_lwf_result['employee_amount'];
\$lwf_emp_amt = 0;
if(\$pay_month == \"process_month_val\"){
\$lwf_emp_amt = \$employee_amount;
}
return \$lwf_emp_amt;
}
//formula PD = MD-LD-DD-S_DD;
//find re-leaving date for resigning employee details check this values 13/12/2021
public function get_seperation_day(\$employee_code,\$role,\$process_month){
\$sep_day = \$this->emp_date_arr[\$employee_code]['last_working_date'];
if(\$sep_day && \$sep_day !== '0000-00-00' && \$sep_day !== '1970-01-01'){
\$month_day_result = \$this->month_day_res[\$role];
\$day_conditions = \$month_day_result['day_conditions'];
\$day_count = \$month_day_result['day_count'];
\$day_start = \$month_day_result['day_start'];
\$day_end = \$month_day_result['day_end'];
if((int)\$day_conditions === 1){
//STATIC DAYS
\$process_date = \$day_count.'-'.\$process_month;
\$process_date = date('d-m-Y', strtotime(\$process_date));
\$process_date = strtotime(\$process_date);
\$sep_day = strtotime(\$sep_day);
\$diff = \$process_date - \$sep_day;
\$separation_day = round(\$diff / 86400);
}else
if((int)\$day_conditions === 2){
//CALENDER DAYS
\$trans_month = explode(\"-\",\$process_month);
\$month_val = \$trans_month[0];
\$year_val = \$trans_month[1];
\$day_count = cal_days_in_month(CAL_GREGORIAN, \$month_val, \$year_val);
\$process_date = \$day_count.'-'.\$process_month;
\$process_date = date('d-m-Y', strtotime(\$process_date));
\$process_date = strtotime(\$process_date);
\$sep_day = strtotime(\$sep_day);
\$diff = \$process_date - \$sep_day;
\$separation_day = round(\$diff / 86400);
}else
if((int)\$day_conditions === 3){
//CUTOFF DAYS
\$trans_month = explode(\"-\",\$process_month);
\$month_val = \$trans_month[0];
\$year_val = \$trans_month[1];
\$process_date = \$day_end.'-'.\$month_val.'-'.\$year_val;
\$process_date = strtotime(\$process_date);
\$sep_day = strtotime(\$sep_day);
\$diff = \$process_date - \$sep_day;
\$separation_day = round(\$diff / 86400);
}
}else{
\$separation_day = 0;
}
if(\$separation_day < 0){
\$separation_day = 0;
}
return \$separation_day;
}
public function get_gratuity(\$role,\$employee_code){
\$check_eligibilty_result = \$this->gratuity_arr[\$role];
\$working_days = \$check_eligibilty_result['working_days'];
\$number_of_years = \$check_eligibilty_result['number_of_years'];
\$pay_days = \$check_eligibilty_result['pay_days'];
\$year_rounding = \$check_eligibilty_result['year_rounding'];
\$gratuity_formula = \$check_eligibilty_result['formula_detail'];
\$formula_rounding = \$check_eligibilty_result['formula_rounding'];
\$gratuity_formula = str_replace(\"@\",\"\",\$gratuity_formula);
\$gratuity_formula = \"(\".\$gratuity_formula.\") as gratuity\";
\$doj = \$this->emp_date_arr[\$employee_code]['date_of_joining'];
\$last_day = \$this->emp_date_arr[\$employee_code]['last_working_date'];
\$status = \$this->emp_date_arr[\$employee_code]['termination_status'];
\$doj = strtotime(\$doj);
\$last_day = strtotime(\$last_day);
\$tot_year = (abs(\$last_day-\$doj)/60/60/24)/365;
//year rounding base working year is rounded
\$year_arr = array(1=>\$tot_year,2=>round(\$tot_year),3=>ceil(\$tot_year),4=>floor(\$tot_year));
if(\$year_rounding){
\$tot_year = \$year_arr[\$year_rounding];
}
\$tot_year = number_format((float)\$tot_year, 2, '.', '');
if(\$last_day && \$last_day !== '0000-00-00' && \$last_day !== '1970-01-01' && (int)\$status === 1){
//gratuity calculations for given formula and employee code
if((\$tot_year >= \$number_of_years)){
\$grat_formula_amt_qry = 'select '.\$gratuity_formula.' from cw_employees where trans_status =1 and employee_code = \"'.\$employee_code.'\"';
\$grat_formula_amt_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$grat_formula_amt_qry')\");
\$grat_formula_amt_result = \$grat_formula_amt_data->result();
\$grat_formula_amt_data->next_result();
\$grat_formula_amt = \$grat_formula_amt_result[0]->gratuity;
\$gratutity_amt = ((\$grat_formula_amt * \$pay_days)/\$working_days) * \$tot_year;
if(\$formula_rounding){
\$gratutity_amt = \$this->rounding_value(\$gratutity_amt,\$formula_rounding);
}
return \$gratutity_amt;
}else{
return 0;
}
}else{
return 0;
}
}
public function get_salary_date(\$category){
\$month_day_result = \$this->gratuity_arr[\$category];
if(\$month_day_result){
\$role = \$month_day_result['category'];
\$day_conditions = \$month_day_result['day_conditions'];
\$day_count = \$month_day_result['day_count'];
\$day_start = \$month_day_result['day_start'];
\$day_end = \$month_day_result['day_end'];
if((int)\$day_conditions === 3){
return array('day_start'=> \$day_start, 'day_end' => \$day_end,'day_conditions'=>(int)\$day_conditions);
}else{
\$day_start = '01';
return array('day_start'=> \$day_start, 'day_end' => \$day_end,'day_conditions'=>(int)\$day_conditions);
}
}
}
//CHECK PTAX FOR EVERY EMPLOYEE FOR THIS MONTH AND YEAR -- 11MAY2019 --Updates
public function get_professional_tax_value(\$employee_code,\$professional_tax_location,\$pt_projection,\$total_earnings,\$process_month){
\$get_emp_sts_result = \$this->emp_date_arr[\$employee_code]['last_working_date'];
\$term_sts = \$get_emp_sts_result['termination_status'];
\$doj = \$get_emp_sts_result['date_of_joining'];
\$professional_tax_amount_db = \$this->get_map_arr['professional_tax_amount']['db_column'];
\$earned_gross_db = \$this->get_map_arr['earned_gross']['db_column'];
\$pt_gross_db = \$this->get_map_arr['pt_gross']['db_column'];
\$ptax_result = \$this->ptax_rslt[\$professional_tax_location];
\$prof_tax_id = \$ptax_result['prime_professional_tax_id'];
\$location = \$ptax_result['location'];
\$calculation_mode = \$ptax_result['calculation_period'];
\$osm_first_st_val = \$ptax_result['osm_first_start'];
\$osm_first_ed_val = \$ptax_result['osm_first_end'];
\$osm_second_st_val = \$ptax_result['osm_second_start'];
\$osm_second_ed_val = \$ptax_result['osm_second_end'];
\$osm_second_end_val = \$ptax_result['osm_second_end'];
\$ptax_deduct_first = \$ptax_result['first_period'];
\$ptax_deduct_second = \$ptax_result['second_period'];
\$trans_month = \$process_month;
\$process_month = explode(\"-\",\$process_month);
\$process_month_val = \$process_month[0];
\$process_year_val = \$process_month[1];
\$process_month_date = \$process_month[1].\"-\".\$process_month[0].\"-01\";
//By Formula every month calculated ptax amount based on earn gross
\$first_period = array();
for(\$osm_first_st_val;\$osm_first_st_val<=\$osm_first_ed_val;\$osm_first_st_val++){
\$first_period[] = \$osm_first_st_val;
}
//array bulid based on months start
\$second_period_part_i = array();
for(\$osm_second_st_val;\$osm_second_st_val<=12;\$osm_second_st_val++){
\$second_period_part_i[] = \$osm_second_st_val;
}
\$second_period_part_ii = array();
for(\$osm_second_ed_val;\$osm_second_ed_val>=1;\$osm_second_ed_val--){
\$second_period_part_ii[] = \$osm_second_ed_val;
}
sort(\$second_period_part_ii);
\$second_period = array_merge(\$second_period_part_i,\$second_period_part_ii);
//array bulid based on months End
//Payroll month fall in first period and second priod start
//Payroll month fall in first period find process and proj count
if(in_array(\$process_month_val,\$first_period ?? [])){
\$act_count = array_search(\$osm_first_ed_val, array_values(\$first_period));
\$cut_off_count = array_search(\$ptax_deduct_first, array_values(\$first_period));
\$cur_count = array_search(\$process_month_val, array_values(\$first_period));
\$cut_off_count++;
\$process_count = (int)\$cut_off_count - (int)\$cur_count;
\$proj_count = (int)\$act_count - (int)\$cur_count;
}else
if(in_array(\$process_month_val,\$second_period ?? [])){
//Payroll month fall in second period find process and proj count
\$act_count = array_search(\$osm_second_end_val, array_values(\$second_period));
\$cut_off_count = array_search(\$ptax_deduct_second, array_values(\$second_period));
\$cur_count = array_search(\$process_month_val, array_values(\$second_period));
\$cut_off_count++;
\$process_count = (int)\$cut_off_count - (int)\$cur_count;
\$proj_count = (int)\$act_count - (int)\$cur_count;
}
if((int)\$process_count < 0){
\$process_count=0;
}
\$prof_tax = 0;
if((int)\$calculation_mode === 1){
//By Formula -- First Method
\$every_month_qry = 'select professional_tax_amount as ptax_amt from cw_professional_tax_tax_range where trans_status=1 and prime_professional_tax_id =\"'.\$prof_tax_id.'\" and earning_range_from <= \"'.floor(\$total_earnings).'\" and earning_range_to >= '.floor(\$total_earnings);
\$every_month_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$every_month_qry')\");
\$every_month_result = \$every_month_data->result();
\$every_month_data->next_result();
\$prof_tax = \$every_month_result[0]->ptax_amt;
}else
if((int)\$calculation_mode === 2){
//\$projection_total = \$pt_projection * (int)\$proj_count; //2 month count
//Find last total earned gross amount
\$projection_total = 0; //2 as discussed with jai sir
//Find last total earned gross amount
\$last_month_date = date(\"Y-m-d\", strtotime(date(\"Y-m-d\", strtotime(\$process_month_date)) . \" - \".\$cur_count.\" months\"));
\$previous_tax_qry = 'select ifnull(sum('.\$pt_gross_db.'),0) as total_earnings from cw_transactions where trans_status =1 and employee_code = \"'.\$employee_code.'\" and date_format(str_to_date(CONCAT(\"01-\",transactions_month), \"%d-%m-%Y\") , \"%Y-%m\") BETWEEN date_format(\"'.\$last_month_date.'\", \"%Y-%m\") and date_format(\"'.\$process_month_date.'\", \"%Y-%m\")';
\$previous_tax_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$previous_tax_qry')\");
\$previous_tax_result = \$previous_tax_data->result();
\$previous_tax_data->next_result();
\$previous_earned = \$previous_tax_result[0]->total_earnings; //upto this month sum earned gross
\$total_earn = \$previous_earned + \$projection_total + \$total_earnings;
//Find current professional tax pay amount
\$find_tax_qry = 'select professional_tax_amount as ptax_amt from cw_professional_tax_tax_range where trans_status = 1 and prime_professional_tax_id =\"'.\$prof_tax_id.'\" and earning_range_from <= \"'.floor(\$total_earn).'\" and earning_range_to >= '.floor(\$total_earn);
\$find_tax_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$find_tax_qry')\");
\$find_tax_result = \$find_tax_data->result();
\$find_tax_data->next_result();
\$find_tax_val = \$find_tax_result[0]->ptax_amt;
//Find last paying professional tax amount and find final professional tax values and return to ptax column
\$last_tax_qry= 'select ifnull(sum('.\$professional_tax_amount_db.'),0) as professional_tax from cw_transactions where trans_status =1 and employee_code = \"'.\$employee_code.'\" and date_format(str_to_date(CONCAT(\"01-\",transactions_month), \"%d-%m-%Y\") , \"%Y-%m\") BETWEEN date_format(\"'.\$last_month_date.'\", \"%Y-%m\") and date_format(\"'.\$process_month_date.'\", \"%Y-%m\")';
\$last_tax_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$last_tax_qry')\");
\$last_tax_result = \$last_tax_data->result();
\$last_tax_data->next_result();
\$last_tax_val = \$last_tax_result[0]->professional_tax;
\$prof_tax = \$find_tax_val - \$last_tax_val;
}else
if((int)\$calculation_mode === 3){
//By Once in six month -- Last Method
//Find Projection amount
//projection amount
//\$projection_total = \$pt_projection * (int)\$proj_count; //2 month count
\$projection_total = 0; //2 as discussed with jai sir
//calculate sum of actual and projection amount and six month amount value
\$last_month_date = date(\"Y-m-d\", strtotime(date(\"Y-m-d\", strtotime(\$process_month_date)) . \" - \".\$cur_count.\" months\"));
\$previous_tax_qry= 'select ifnull(sum('.\$pt_gross_db.'),0) as total_earnings from cw_transactions where trans_status =1 and employee_code = \"'.\$employee_code.'\" and date_format(str_to_date(CONCAT(\"01-\",transactions_month), \"%d-%m-%Y\") , \"%Y-%m\") BETWEEN date_format(\"'.\$last_month_date.'\", \"%Y-%m\") and date_format(\"'.\$process_month_date.'\", \"%Y-%m\")';
\$previous_tax_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$previous_tax_qry')\");
\$previous_tax_result = \$previous_tax_data->result();
\$previous_tax_data->next_result();
\$previous_earned = \$previous_tax_result[0]->total_earnings; //find curr to pre earned gross sum value
\$total_earn =\$previous_earned + \$projection_total + \$total_earnings; // total sum (earned + project)
//Find current professional tax pay amount
\$find_tax_qry = 'select professional_tax_amount as ptax_amt from cw_professional_tax_tax_range where trans_status=1 and prime_professional_tax_id =\"'.\$prof_tax_id.'\" and earning_range_from <= \"'.floor(\$total_earn).'\" and earning_range_to >= '.floor(\$total_earn);
\$find_tax_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$find_tax_qry')\");
\$find_tax_result = \$find_tax_data->result();
\$find_tax_data->next_result();
\$prof_tax = \$find_tax_result[0]->ptax_amt;
}
if(\$prof_tax < 0 || \$total_earnings < \$prof_tax){
\$prof_tax = 0;
}
return round(\$prof_tax);
}
\n\n}
\n?>";
$oldmask = umask(0);
$formula_temp_file = dirname(__FILE__)."/"."Fandf_calculation_model.php";
$formula_temp_file = str_replace('controllers','models',$formula_temp_file);
fopen("$formula_temp_file", "w");
file_put_contents("$formula_temp_file",$final_code);
chmod($formula_temp_file, 0777);
umask($oldmask);
}
}
//VIEW FUNCTION FOR PAYROLL
public function transaction_data($process_month,$process_role,$process_emp_code){
$search_val = " and cw_transactions.fandf = 1"; // fandf [MS 19-10-2024]
if($process_role){
$search_val = ' and cw_transactions.'.$this->payroll_process_type.' in ('.$process_role.') ';
}else
if($process_emp_code){
$qry = "";
$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,gross_check,deduction_check,transaction_type from cw_form_setting where prime_module_id = "employees" and transaction_type in (1,2,3) '.$qry.' and trans_status = "1" order by gross_check,deduction_check 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;
$form_arr = array();
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;
$form_arr[$label_name] = $form;
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,' , ');
//dont delete ragu remove this
//cw_transactions.stop_pay_status !=1 and
$trans_status = " left join cw_employees on cw_employees.employee_code =cw_transactions.employee_code where ".$table_name.".trans_status = 1 AND cw_transactions.termination_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($final_qry);
$final_result = $final_data->result();
$final_data->next_result();
if($process_emp_code){
$earning_line = "";
$deduction_line = "";
foreach($final_result[0] as $key => $value){
$formrslt = $form_arr[$key];
$view_name = $formrslt->view_name;
$label_name = $formrslt->label_name;
$gross_check = (int)$formrslt->gross_check;
$ded_check = (int)$formrslt->deduction_check;
if($key === 'emp_name'){
$emp_name = $value;
}
$clr = "";
if($gross_check){
$clr = "background-color:#e3f7e4";
}else
if($ded_check){
$clr = "background-color:#f1dadc";
}
if($value !== "0.00"){
if($label_name === 'total_earnings'){
$total_earnings = $value;
}else
if($label_name === 'total_deductions'){
$total_deductions = $value;
}else
if($label_name === 'net_pay'){
$net_pay = $value;
}else
if((int)$formrslt->transaction_type === 2){
$earning_line .= "<tr class='tlf'><td style='vertical-align: text-top;$clr'>$view_name</td><td style='vertical-align: text-top;$clr'>$value</td></tr>";
}else
if((int)$formrslt->transaction_type === 3){
$deduction_line .= "<tr class='tlf'><td style='vertical-align: text-top;$clr'>$view_name</td><td style='vertical-align: text-top;$clr'>$value</td></tr>";
}
}
}
$table_content = "<div class='col-md-12' style='box-shadow: 0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2);'><div class='col-md-4'></div><div class='col-md-4' style='text-align: center;'><h5 style='font-weight:bold;color:blue;'>Payroll Breakup For the Month of $process_month </h5><h6 style='font-weight:bold;color:#000;'> $process_emp_code - $emp_name </h6></div><table class='table' style='box-shadow:none;'><tbody>
<tr>
<td style='vertical-align: text-top;'>
<table class='table' style='box-shadow:none;'>
<thead class='tlf'>
<tr><th>Earnings</th><th>Amount / Count</th></tr>
</thead>
<tbody class='tlf' style='overflow-y: scroll;display: block;max-height: 300px !important;'>
$earning_line
</tbody>
</table>
</td>
<td style='vertical-align: text-top;'>
<table class='table' style='box-shadow:none;margin-left: -16px;'>
<thead class='tlf'>
<tr><th>Deductions</th><th>Amount / Count</th></tr>
</thead class='tlf'>
<tbody style='overflow-y: scroll;display: block;max-height: 300px !important;'>
$deduction_line
</tbody>
</table>
</td>
</tr>
<tr>
<table class='table' style='box-shadow:none;'>
<tbody>
<tr><td style='text-align: center;font-weight:bold;color:blue;'>Total Earnings</td><td style='text-align: center;font-weight:bold;color:blue;'>Total Deduction</td><td style='text-align: center;font-weight:bold;color:blue;'>Net Pay</td></tr>
<tr><td style='text-align: center;font-weight:bold;'>$total_earnings</td><td style='text-align: center;font-weight:bold;'>$total_deductions</td><td style='text-align: center;font-weight:bold;'>$net_pay</td></tr>
</tbody>
</table>
</tr></tbody></table>
</div>";
}else{
$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;
//return json_encode(array('success' => TRUE,'table_content' => $table_content));
}
public function export_columns(){
$final_qry = "select * from cw_transactions";
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result_array();
$final_data->next_result();
$columnValues = Array();
foreach($final_result[0] as $rslt => $value){
if(($rslt !== "prime_transactions_id") && ($rslt !== "employees_id")){
if($rslt === "process_month"){
break;
}
$rslt = ucfirst(str_replace("_"," ",$rslt));
$columnValues[] = $rslt;
}
}
$filename = $this->control_name."_".date('d-m-Y').".csv";
$fp = fopen('php://output', 'w');
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
fputcsv($fp, $columnValues);
return json_encode(array('success' => TRUE));
exit;
}
//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));
}
//excel maaping format
public function excel($module_id,$excel_format){
$excel_format_qry = 'select view_name,excel_line_column_name,excel_line_value from cw_util_excel_format_line inner join cw_form_setting on cw_form_setting.label_name = excel_line_column_name where excel_line_module_id = "'.$module_id.'" and prime_excel_format_id ="'.$excel_format.'" and cw_util_excel_format_line.trans_status = 1 GROUP BY cw_form_setting.label_name';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
// require_once APPPATH."/controllers/php_excel/PHPExcel.php";;
$obj = new Spreadsheet();
//Set the first row as the header row
foreach($excel_result as $excel){
$excel_line_column_name = $excel->view_name;
$excel_line_value = $excel->excel_line_value;
$obj->getActiveSheet()->setCellValue($excel_line_value."1", $excel_line_column_name);
}
// Rename worksheet name
$filename= $module_id.".xls"; //save our workbook as this file name
ob_end_clean();
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
ob_end_clean();
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($obj, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
echo json_encode(array('success' => TRUE, 'output' => $excelOutput));
}
//SAVE IMPORT FILE PATH
public function save_import(){
$module_id = $this->control_name;
$process_month = $this->input->post('transaction_month');
$excel_format = $this->input->post('excel_format');
$excel_file_path = $this->input->post('excel_file_path');
$excel_sheet_name = $this->input->post('excel_sheet_name');
$excel_start_row = $this->input->post('excel_start_row');
$excel_end_row = $this->input->post('excel_end_row');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$import_query = 'insert into cw_month_import (module_id,excel_format,excel_file_path,excel_sheet_name,excel_start_row,excel_end_row,process_month,trans_created_by,trans_created_date) value ("'.$module_id.'","'.$excel_format.'","'.$excel_file_path.'","'.$excel_sheet_name.'","'.$excel_start_row.'","'.$excel_end_row.'","'.$process_month.'","'.$logged_id.'","'.$today_date.'")';
$import_info = $this->db->query("CALL sp_a_run ('INSERT','$import_query')");
$import_result = $import_info->result();
$import_info->next_result();
$import_id = $import_result[0]->ins_id;
echo $this->do_excel_payroll_import($import_id);
}
//IMPORT DATA FROM FILE PATH
public function do_excel_payroll_import($import_id){
$filename = dirname(__FILE__)."/php_excel/PHPExcel/IOFactory.php";
include($filename);
if($import_id < 0){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}
$excel_path_qry = 'select * from cw_month_import where import_id = "'.$import_id.'"';
$excel_path_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_path_qry')");
$excel_path_result = $excel_path_info->result();
$excel_path_info->next_result();
if(!$excel_path_result){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}else{
$this->prime_table = "cw_transactions";
$excel_file_path = $excel_path_result[0]->excel_file_path;
$module_id = "transactions";
$excel_format = $excel_path_result[0]->excel_format;
$excel_sheet_name = (int)$excel_path_result[0]->excel_sheet_name;
$excel_row_start = (int)$excel_path_result[0]->excel_start_row;
$excel_row_end = (int)$excel_path_result[0]->excel_end_row;
$process_month = $excel_path_result[0]->process_month;
$lock_pay_qry = 'select * from cw_payroll where pay_month = "'.$process_month.'" and status = 1 and trans_status = 1';
$lock_pay_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_pay_qry')");
$lock_num_rows = $lock_pay_data->num_rows();
$lock_pay_data->next_result();
if((int)$lock_num_rows > 0){
return json_encode(array('success' => false, 'message' => "payroll is locked for this month, please unlocked to upload the files"));
exit(0);
}
/*$payroll_exist_query = 'select count(*) as pay_count from cw_transactions where transactions_month="'.$process_month.'" and fandf = 2';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('RUN','$payroll_exist_query')");
$payroll_exist_result = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$payroll_exist_count = $payroll_exist_result[0]->pay_count;
if((int)$payroll_exist_count > 0){
return json_encode(array('success' => false, 'message' => "Already Payroll is proceed this month!!!"));
exit(0);
}*/
$format_qry = 'select * from cw_util_excel_format where prime_excel_format_id = "'.$excel_format.'" and cw_util_excel_format.trans_status = 1';
$format_info = $this->db->query("CALL sp_a_run ('SELECT','$format_qry')");
$format_rslt = $format_info->result();
$format_info->next_result();
if(!$format_rslt){
return json_encode(array('success' => false, 'message' => "Please add excel format before import"));
}else{
$exist_column_name = explode(",",$format_rslt[0]->exist_column_name ?? "");
$excel_format_qry = 'select unique_field,pick_list_import,view_name,label_name,field_type,pick_table,pick_list_type,pick_list,mandatory_field,field_isdefault,excel_line_column_name,excel_line_value from cw_util_excel_format_line inner join cw_form_setting on label_name = excel_line_column_name where excel_line_module_id = "'.$module_id.'" and prime_excel_format_id = "'.$excel_format.'" and cw_form_setting.prime_module_id = "employees" and cw_util_excel_format_line.trans_status = 1 order by prime_excel_format_line_id ASC';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_format_result = $excel_format->result();
$excel_format->next_result();
if(!$excel_format_result){
return json_encode(array('success' => false, 'message' => "Please map excel cell column before import"));
}else{
try{
$excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($excel_file_path);
}catch(Exception $e){
die('Error loading file "' . pathinfo($excel_file_path, PATHINFO_BASENAME). '": ' . $e->getMessage());
return json_encode(array('success' => false, 'message' => "Invalid file or path"));
}
$sheet = $excel_obj->getSheet($excel_sheet_name);
if($excel_row_end){
$total_rows = $excel_row_end;
}else{
$total_rows = $sheet->getHighestRow();
}
$highest_column = $sheet->getHighestColumn();
$status_array = array();
$invalid_cat = array();
$formula_process = array();
//Unique field check
$err_column_tabview = array();
$array_uniq = array();
foreach ($excel_format_result as $key => $value) {
$label_name = $value->label_name;
$view_name = $value->view_name;
$unique_field = $value->unique_field;
$field_type = $value->field_type;
$pick_table = $value->pick_table;
$pick_list_type = (int)$value->pick_list_type;
$excel_line_value = $value->excel_line_value;
$pick_list_import = $value->pick_list_import;
$pick_list = $value->pick_list;
$multi_get_cell_value = $sheet->rangeToArray("$excel_line_value$excel_row_start:$excel_line_value$total_rows", NULL, TRUE, TRUE, TRUE);
$common_multi_cell_value = $sheet->rangeToArray("$excel_line_value$excel_row_start:$excel_line_value$total_rows", NULL, TRUE, TRUE, TRUE);
$i = $excel_row_start;
foreach($common_multi_cell_value as $common_value){
foreach($common_value as $col_key =>$col_value){
if(empty($col_value) && !is_numeric($col_value)){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "columns are empty and invalid data is present please check it?";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}elseif((int)$field_type === 5 || (int)$field_type === 7){
if($pick_list_import !== 1){
if($pick_list_type === 1){
if($excel_line_column_name === "employee_code" || $excel_line_column_name === "user_id"){
$emp_code_qry = 'select count(*) as rslt_count from cw_employees where trans_status = 1 and employee_code = "'.$col_value.'"';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_code_qry')");
$emp_data_result = $emp_data->result();
$emp_data->next_result();
$rslt_count = $emp_data_result[0]->rslt_count;
if((int)$rslt_count === 0){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "is not exit in employee master please check it?";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}else
if($pick_table === "cw_category"){
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query = 'select '.$pick_list.' from '.$pick_table.' where '.$pick_list_val_2.' = "'.$col_value.'"';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$pick_count = $pick_data->num_rows();
$pick_val_1 = $pick_result[0]->$pick_list_val_1;
if((int)$pick_count === 0){
$sts = 0;
$invalid_cat[] = $col_value;
}else{
$col_value = $pick_val_1;
}
$cat_inv = array_unique($invalid_cat);
$inv_cat = implode(",",$cat_inv ?? []);
$cat_sts = 1;
if(empty($inv_cat)){
$cat_sts = 0;
}
if($cat_sts){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "Invalid category name like $inv_cat, please check category and employee code!!!";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}else{
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query = 'select '.$pick_list.' from '.$pick_table.' where '.$pick_list_val_2.' = "'.$col_value.'"';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_count = $pick_data->num_rows();
$pick_data->next_result();
if((int)$pick_count === 0){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "column invalid data is present please check it?";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}
}
}
}elseif($field_type === 4){
$excel_cell_formate = $excel_obj->getActiveSheet()->getCell($col_key.$i)->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']["$excel_line_value$i"] = $view_name;
$msg_line = "Please enter valid date... Please map The Date Format Like (DD/MM/YYYY)";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}else
if((int)$month > 12){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "Invalid Month... Please map The Date Format Like (DD/MM/YYYY)";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}else
if((int)$date > (int)$tot_days){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "Invalid date... Please map The Date Format Like (DD/MM/YYYY)";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}else{
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "Invalid Date Format... Please map The Date Format Like (DD/MM/YYYY)";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}
$i++;
}
}
/*if((int)$unique_field === 1){
$get_duplicat_value = $sheet->rangeToArray("$excel_line_value$excel_row_start:$excel_line_value$total_rows", NULL, TRUE, TRUE, TRUE);
$get_duplicat_value = array_map('array_filter', $get_duplicat_value);//empty remove
$get_duplicat_value = array_filter($get_duplicat_value);
foreach ($get_duplicat_value as $current_key => $current_array) {
$get_excel_val = $current_array[$excel_line_value];
if($label_name === 'employee_code'){
$exist_query = 'select count(*) uniq_exist_count from '.$this->prime_table.' where '.$label_name.' = "'.$get_excel_val.'" and trans_status =1 and transactions_month="'.$process_month.'"';
}else{
$exist_query = 'select count(*) uniq_exist_count from '.$this->prime_table.' where '.$label_name.' = "'.$get_excel_val.'" and trans_status =1';
}
$exist_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
foreach ($get_duplicat_value as $search_key => $search_array) {
if($search_array["$excel_line_value"] == $current_array["$excel_line_value"]){
if ($search_key != $current_key) {
$err_column_array['error']["$excel_line_value$current_key"] = $view_name;
$msg_line = "duplicate data present in column, please check it?";
$err_column_tabview['error']["$excel_line_value$current_key"] = $view_name." ".$msg_line;
}
}
$exist_count = $exist_result[0]->uniq_exist_count;
$array_uniq[$label_name]['view_name'] = $view_name;
$array_uniq[$label_name]['label_name'] = $label_name;
}
if((int)$exist_count > 0){
$err_column_array['error']["$excel_line_value$current_key"] = $view_name;
$msg_line = "Data already exists for this Column";
$err_column_tabview['error']["$excel_line_value$current_key"] = $view_name." ".$msg_line;
}
}
}*/
}
// get module unique column
$uniq_field_qry = 'select view_name,label_name from cw_form_setting where cw_form_setting.prime_module_id = "'.$module_id.'" and input_view_type IN(1,2) and field_show = 1 and unique_field = 1 and trans_status = 1';
$uniq_field_info = $this->db->query("CALL sp_a_run ('SELECT','$uniq_field_qry')");
$uniq_field_rslt = $uniq_field_info->result_array();
$uniq_field_info->next_result();
foreach($uniq_field_rslt as $arr){
$uniq_field_rslt[$arr['label_name']] = $arr;
}
$uniq_result = array_diff_key($uniq_field_rslt,$array_uniq);
if((int)$import_type === 1){
foreach ($uniq_result as $uniq_key => $uniq_val) {
$label_name = $uniq_val['label_name'];
$err_column_array['error']["label_name"] = $uniq_val['view_name'];
$msg_line = "Unique Field Column Missing";
$err_column_tabview['error'][$uniq_val['view_name']] = $uniq_val['view_name']." ".$msg_line;
}
}
$err_column_count = count($err_column_array['error'] ?? []);
$err_column = implode(",",(array_unique($err_column_array['error'] ?? [])) ?? []);
if((int)$err_column_count > 0){
$table_info = $this->get_excel_error_ui($err_column_tabview);
echo json_encode(array('success'=>false,'message'=>"Column Wise Error",'table_info'=>$table_info));
exit();
}
for($row =$excel_row_start; $row <= $total_rows; $row++){
$prime_column_val = "";
$prime_cell_val = "";
$prime_update_val = "";
$exist_val = "";
$status_info = array();
$status_info["Excel Row"] = $row;
$sts = 1;
foreach($excel_format_result as $excel_info){
$field_isdefault = (int)$excel_info->field_isdefault;
$mandatory_field = (int)$excel_info->mandatory_field;
$field_type = (int)$excel_info->field_type;
$pick_table = $excel_info->pick_table;
$pick_list_type = (int)$excel_info->pick_list_type;
$pick_list = $excel_info->pick_list;
$excel_line_column_name = $excel_info->excel_line_column_name;
$excel_line_value = $excel_info->excel_line_value;
$get_cell_value = trim($sheet->getCell("$excel_line_value$row")->getCalculatedValue());
// FOR DATE
if($field_type === 4){
$get_cell_value = trim(date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell("$excel_line_value$row")->getCalculatedValue())));
}else
// FOR PICKLIST CHECK
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
//Already role is there or not
if($pick_table === "cw_category"){
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query = 'select '.$pick_list.' from '.$pick_table.' where '.$pick_list_val_2.' = "'.$get_cell_value.'"';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$pick_count = $pick_data->num_rows();
$pick_val_1 = $pick_result[0]->$pick_list_val_1;
if((int)$pick_count === 0){
$sts = 0;
$invalid_cat[] = $get_cell_value;
}else{
$get_cell_value = $pick_val_1;
}
}
}
}
if($field_isdefault === 1){
if($excel_line_column_name === "employee_code"){
$employee_code = $get_cell_value;
}
$prime_column_val .= $excel_line_column_name.",";
$prime_cell_val .= '"'.$get_cell_value.'",';
$prime_update_val .= $excel_line_column_name.'='.'"'.$get_cell_value.'",';
if(empty($exist_column_name)) {
if($mandatory_field === 1){
$exist_val .= $excel_line_column_name.' = "'.$get_cell_value.'" and ';
}
}else{
if(in_array($excel_line_column_name,$exist_column_name ?? [])){
$exist_val .= $excel_line_column_name.' = "'.$get_cell_value.'" and ';
}
}
}
}
if((int)$sts !== 0){
//GET Employee id
$emp_query = 'select * from cw_employees where employee_code = "'.$employee_code.'" and trans_status =1';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_query')");
$emp_result = $emp_info->result();
$emp_info->next_result();
$employees_id = $emp_result[0]->prime_employees_id;
$emp_name = $emp_result[0]->emp_name;
$doj = $emp_result[0]->date_of_joining;
$esi_elig = $emp_result[0]->esi_eligibility;
$pf_elig = $emp_result[0]->pf_eligibility;
$lwf_location = $emp_result[0]->lwf_location;
$tax_location = $emp_result[0]->professional_tax_location;
if(!$employees_id){
return json_encode(array('success' => false, 'message' => "Employee Code Not Exist.. Please Map the Employee Code Correctly"));
}else{
//only role and employee code is checking
if($prime_column_val){
$prime_id = "prime_".$module_id."_id";
$exist_val = "and ".rtrim($exist_val," and ");
$exist_query = 'select count(*) as exist_count from cw_transactions where cw_transactions.trans_status =1 and transactions_month = "'.$process_month.'" '.$exist_val;
$exist_info = $this->db->query("CALL sp_a_run ('RUN','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
$exist_count = $exist_result[0]->exist_count;
$created_on = date("Y-m-d H:i:s");
if((int)$exist_count === 0){
$prime_column_val .= "employees_id,emp_name,date_of_joining,esi_eligibility,pf_eligibility,lwf_location,professional_tax_location,transactions_month,process_month,trans_created_by,trans_created_date";
$prime_cell_val .= '"'.$employees_id.'","'.$emp_name.'","'.$doj.'","'.$esi_elig.'","'.$pf_elig.'","'.$lwf_location.'","'.$tax_location.'","'.$process_month.'","'.$process_month.'","'.$this->logged_id.'",'.'"'.$created_on.'"';
$prime_column_val = rtrim($prime_column_val,",");
$prime_cell_val = rtrim($prime_cell_val,",");
$prime_query = "insert into $this->prime_table ($prime_column_val) VALUES ($prime_cell_val)";
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$formula_process[] = $insert_id;
$status_info['Status'] = "Inserted to DB";
}else{
$prime_update_val .= 'trans_updated_by="'.$this->logged_id.'",trans_updated_date="'.$created_on.'"';
$prime_query = 'UPDATE '.$this->prime_table.' SET '.$prime_update_val.' where cw_transactions.trans_status =1 and transactions_month = "'.$process_month.'" '.$exist_val;
$upd_info = $this->db->query("CALL sp_a_run ('UPDATE','$prime_query')");
$status_info['Status'] = "Updated to DB";
}
$status_array[] = $status_info;
}
}
}
}
$cat_inv = array_unique($invalid_cat);
$inv_cat = implode(",",$cat_inv ?? []);
$cat_sts = 1;
if(empty($inv_cat)){
$cat_sts = 0;
}
if($cat_sts){
echo json_encode(array('success'=>false,'message'=>"File not inserted in database",'cat_sts'=>$cat_sts, 'warning'=>"Invalid category name like $inv_cat, please check category and employee code!!!"));
}else{
echo json_encode(array('success'=>true,'message'=>"Successfully file imported"));
}
}
}
}
}
//ERROR COLUMN AND ROW DISPLAY FOR INVALID DATAS
public function get_excel_error_ui($err_column_tabview){
$table_info = "";
$th_line = "";
$tr_line = "";
foreach($err_column_tabview as $err_column){
foreach($err_column as $key => $value){
$tr_line .= "<tr><td>$key</td><td style='color:#ff0303 !important;'>$value</td></tr>";
}
}
$table_info = "<table class='table table-bordered'>
<thead>
<tr>
<th>Row and Column</th>
<th>Input Column</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $table_info;
}
//Increment Update
public function increment_update($emp_code,$process_month,$role){
$increment_column_qry = 'select * from cw_increment where employee_code="'.$emp_code.'" and apply_on="'.$process_month.'"';
$increment_column_info = $this->db->query("CALL sp_a_run ('SELECT','$increment_column_qry')");
$increment_column_result = $increment_column_info->result();
$increment_column_info->next_result();
//check formula
$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 ="increment" 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;
$logged_id = $this->session->userdata('logged_id');
$update_date = date("Y-m-d H:i:s");
$update_qry = "";
$trans_array = array();
$column_array = array();
$new_array = array();
foreach($increment_column_result as $incr_rslt){
$emp_code = $incr_rslt->employee_code;
$column_name = $incr_rslt->column_name;
$new_value = $incr_rslt->new_value;
$diff_value = $incr_rslt->difference_value;
if((int)$formula_exist > 0){
$column_array[] = $column_name;
$new_array[] = $new_value;
}else{
if((int)$diff_value !== 0){
$update_qry .= $column_name.'="'.$new_value.'",';
}
}
}
$trans_array[$emp_code] = array_combine($column_array,$new_array);
if((int)$formula_exist > 0){
$trans_array[$emp_code]['role'] = $role;
$new_array = $this->Increment_calculation_model->increment_calculation($trans_array,$emp_code);
foreach($new_array[$emp_code] as $key => $value){
if($key !== "role"){
$update_qry .= $key.'="'.$value.'",';
}
}
}
$update_qry .= 'trans_updated_by = "'.$logged_id.'", trans_updated_date = "'.$update_date.'"';
$upd_inc_query = 'UPDATE cw_employees SET '.$update_qry.' WHERE employee_code = "'.$emp_code.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_inc_query')");
return true;
}
//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);
}
// NEHA EDIT 07-DEC-2019
//PAYROLL PROCESS GET ACTIVITY REMINDER INFO MODAL VIEW
public function get_activity_remainder(){
$process_month = $this->input->post('process_month');
$mode_qry = 'select prime_remainder_mode_id,remainder_mode_value from cw_remainder_mode where cw_remainder_mode.trans_status = 1';
$mode_qry_info = $this->db->query("CALL sp_a_run ('SELECT','$mode_qry')");
$mode_qry_result = $mode_qry_info->result();
$mode_qry_info->next_result();
foreach($mode_qry_result as $mode_rslt){
$prime_remainder_mode_id = $mode_rslt->prime_remainder_mode_id;
$remainder_mode_value = $mode_rslt->remainder_mode_value;
$status_line .="<option value='$prime_remainder_mode_id' $selected>$remainder_mode_value</option>";
}
$qry = 'select prime_activity_remainder_id,apply_month,cw_activity_remainder.employee_code as employee_code,remainder_status,cw_employees.emp_name as emp_name,remainder_details from cw_activity_remainder inner join cw_employees on cw_employees.employee_code = cw_activity_remainder.employee_code where remainder_status = 1 and cw_activity_remainder.trans_status = 1 and apply_month ="'.$process_month.'"';
$activity_rem_info = $this->db->query("CALL sp_a_run ('SELECT','$qry')");
$activity_rem_result = $activity_rem_info->result();
$activity_rem_info->next_result();
$activity_rem_count = $activity_rem_info->num_rows();
foreach($activity_rem_result as $rslt){
$apply_month = $rslt->apply_month;
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$remainder_status = $rslt->remainder_status;
$remainder_details = $rslt->remainder_details;
if($remainder_status === 1){
$selected = "selected";
}
$active_id = $rslt->prime_activity_remainder_id;
$activity_line .= "<tr>
<td>$apply_month</td>
<td>$employee_code</td>
<td>$emp_name</td>
<td>$remainder_details</td>
<td><select name='remainder_status[]' id='remainder_status_$active_id'>
$status_line
</select></td>
<td>
<a class='btn btn-primary btn-sm' onclick='edit_active_info($active_id)'>Submit</a>
</td>
</tr>";
}
$table_data = "<table class='table table-striped table-bordered' id='activity_rem_table'>
<thead>
<tr>
<th scope='col'>Apply Month</th>
<th scope='col'>Employee Code</th>
<th scope='col'>Employee Name</th>
<th scope='col'>Remainder Details</th>
<th scope='col'>Remainder status</th>
<th scope='col'>Action</th>
</tr>
</thead>
<tbody>
$activity_line
</tbody>
</table>";
echo json_encode(array("success" => TRUE,'table_data' => $table_data,'sts_count'=>$activity_rem_count));
}
public function inc_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 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 = "";
$formula_code = "";
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;
$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,";
}
}
}
$formula_code .= "\n\t\t\t $input_colum \n\t\t\t";
}
$oldmask = umask(0);
$fname = 'increment_calculation($trans_array,$employee_code){';
//$emp_code = "\n\t\t\t".'$employee_code = "'.$employee_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);
}
//EDIT ACTIVITY REMINDER INFO
public function edit_active_info(){
$active_id = $this->input->post('prime_activity_remainder_id');
$remainder_status = $this->input->post('remainder_status');
$logged_id = $this->session->userdata('logged_id');
$update_date = date("Y-m-d H:i:s");
$upd_qry = 'remainder_status = "'.$remainder_status.'"';
$update_qry .= 'trans_updated_by = "'.$logged_id.'", trans_updated_date = "'.$update_date.'"';
$upd_inc_query = 'UPDATE cw_activity_remainder SET '.$upd_qry.' WHERE prime_activity_remainder_id = "'.$active_id.'"';
if($this->db->query("CALL sp_a_run ('RUN','$upd_inc_query')")){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Updated",'prime_activity_remainder_id'=> $active_id ));
}else{
echo json_encode(array('success' => FALSe, 'message' => "Update failed, Please Try Again"));
}
}
//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();
$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')");
}
}
}
public function validation_page(){
$err_info = array();
//check formula exit
$payroll_formula_ext_qry = 'select count(*) as formula_count from cw_payroll_formula where trans_status =1';
$payroll_formula_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_formula_ext_qry')");
$payroll_formula_result = $payroll_formula_data->result();
$payroll_formula_data->next_result();
$formula_count = $payroll_formula_result[0]->formula_count;
if((int)$formula_count === 0){
$err_info['payroll_formula'] = "No formula is available, please write formula for category?";
}
$fixed_pf_data = $this->Hr_methods_model->get_mapped_db_column(3);
$fixed_pf_db = $fixed_pf_data['fixed_pf'];
if(!$fixed_pf_db){
$fixed_pf_db = "fixed_pf";
}
//FOR FORMULA REQUIRED COLUMNS ARRAY
$out_col_exist_arr = array("fpt_gross" => "fpt_gross","fixed_pf" => "$fixed_pf_db");
$colum_exist_count = count($out_col_exist_arr ?? []);
$out_col_exist = implode('","',$out_col_exist_arr ?? []);
//GET LABEL NAME FOR SHOW ERROR ON TABLE (GET LABEL NAME FOR FORMULA REQUIRED COLUMNS)
$label_name_query = 'SELECT label_name,view_name FROM cw_form_setting WHERE label_name IN ("'.$out_col_exist.'") AND trans_status="1"';
$label_name_info = $this->db->query("CALL sp_a_run ('SELECT','$label_name_query')");
$label_name_rslt = $label_name_info->result();
$label_name_info->next_result();
$col_label_name_arr = array_column($label_name_rslt, 'view_name','label_name');
//QUERY FOR FORMULA EXIST OR NOT (GET FORMULA DETAILS FOR FORMULA REQUIRED COLUMNS)
$formula_check_qry ='SELECT formula_for,out_column FROM cw_payroll_formula WHERE out_column IN ("'.$out_col_exist.'") AND trans_status="1" ORDER BY formula_for ASC';
$check_info = $this->db->query("CALL sp_a_run ('SELECT','$formula_check_qry')");
$check_formula_rslt = $check_info->result();
$check_info->next_result();
$check_count = count($check_formula_rslt ?? []);
//LOOPING FOR EXIST DATA IN PAYROLL FORMULA TABLE BASED ON out_col_exist_arr
$formula_exist_arr = array();
foreach($check_formula_rslt as $rslt_key => $rslt_value){
$cat_formula_for = $rslt_value->formula_for;
$formula_out_col = $rslt_value->out_column;
$formula_exist_arr[$cat_formula_for]["category_id"] = $cat_formula_for;
$formula_exist_arr[$cat_formula_for]["out_column"][$formula_out_col] = $formula_out_col;
}
//QUERY FOR CATEGORY LIST
$categ_count_qry = 'SELECT prime_category_id,category_name FROM cw_category WHERE trans_status=1 AND prime_category_id !="1"';
$categ_info = $this->db->query("CALL sp_a_run ('SELECT','$categ_count_qry')");
$categ_count_rslt = $categ_info->result_array();
$check_info->next_result();
$cat_array = array_column($categ_count_rslt,'category_name','prime_category_id');
foreach($cat_array as $cat_id => $cat_name){
$out_col_arr = $formula_exist_arr[$cat_id]['out_column'];
//ERROR CHECK
if(count($out_col_arr ?? []) < $colum_exist_count){
$err_out_col_arr = array();
foreach($out_col_exist_arr as $out_col){
if(!in_array($out_col,$out_col_arr ?? [])){
$err_out_col_arr[] = $col_label_name_arr[$out_col];
}
}
$err_out_cols = implode(',',$err_out_col_arr ?? []);
//ERROR MESSAGE
$err_info[$cat_name] = "Please Create Payroll Formula for the column <span style = 'color:red;'>$err_out_cols</span> to this Component <span style = 'color:red;'>$cat_name</span>";
}
}
//check function exit
$payroll_function_ext_qry = 'select count(*) as function_count from cw_payroll_function where trans_status =1';
$payroll_function_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_function_ext_qry')");
$payroll_function_result = $payroll_function_data->result();
$payroll_function_data->next_result();
$function_count = $payroll_function_result[0]->function_count;
if((int)$function_count === 0){
$err_info['payroll_function'] = "No function is available at least map total working days, differential days, separation days for default settings";
}
//check function column mapping exit
/*$payroll_function_map_ext_qry = 'select count(*) as function_map_count from cw_payroll_function_map where trans_status =1';
$payroll_function_map_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_function_map_ext_qry')");
$payroll_function_map_result = $payroll_function_map_data->result();
$payroll_function_map_data->next_result();
$function_map_count = $payroll_function_map_result[0]->function_map_count;
if((int)$function_map_count === 0){
$err_info['payroll_function_map'] = "No function map is available";
}*/
return $err_info;
}
//ERROR TABLE SHOW FUNCTION
public function validation_ui($error_info){
$tr_line = "";
foreach($error_info as $key => $msg){
$type = ucwords(str_replace("_"," ",$key));
$tr_line .= "<tr $color><td>$type</td><td>$msg</td></tr>";
}
$thead_line .="<tr><td colspan='2' style='background-color:white; color:red;'>Please Do the Default Settings Before Processing the Payroll!!!
</td></tr>";
$table_info = "<table class='table table-bordered'>
<thead>
$thead_line
<tr>
<th>Category</th>
<th>Error Details</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $table_info;
}
public function check_import_month(){
$transaction_month = $this->input->post("transaction_month");
$role = $this->input->post("category");
$payroll_exist_qry = 'select count(*) as exist_count from cw_transactions where cw_transactions.trans_status =1 and role = "'.$role.'" and transactions_month = "'.$transaction_month.'" and fandf = 2';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('SELECT','$payroll_exist_qry')");
$payroll_exist_rslt = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$exist_count = $payroll_exist_rslt[0]->exist_count;
if((int)$exist_count === 0){
echo json_encode(array("success" => TRUE,'message' => 'Ok Proceed'));
}else{
echo json_encode(array("success" => FALSE,'message' => 'Payroll Already Exist This Month'));
}
}
//Get ESI STATUS 20SEP2022 BSK
public function update_esi_status($where_query,$process_month,$middle_inc){
$get_statutory = 'SELECT category,esi_limit,esi_conditions,esi_start_check_month,esi_end_check_month,esi_eligibilit_formula FROM cw_statutory inner join cw_employees on cw_employees.role = cw_statutory.category WHERE cw_statutory.trans_status = 1 '.$where_query.' GROUP BY cw_statutory.category';
$statutory_data = $this->db->query("CALL sp_a_run ('SELECT','$get_statutory')");
$statutory_result = $statutory_data->result();
$statutory_data->next_result();
//get function mapping data
$get_map_qry = 'select * from cw_payroll_function_map where trans_status=1';
$get_map_info = $this->db->query("CALL sp_a_run ('SELECT','$get_map_qry')");
$get_map_rslt = $get_map_info->result_array();
$get_map_info->next_result();
foreach($get_map_rslt as $arr){
$get_map_arr[$arr['loc_name']] = $arr;
}
$arr_gross_col = $get_map_arr['arrear_gross']['db_column'];
foreach ($statutory_result as $key => $value){
$category = (int)$value->category;
$esi_limit = (int)$value->esi_limit;
$esi_conditions = (int)$value->esi_conditions;
$esi_start_check_month = (int)$value->esi_start_check_month;
$esi_end_check_month = (int)$value->esi_end_check_month;
$formula = str_replace("@","",$value->esi_eligibilit_formula);
if(! is_array($process_month)){
$process_month = explode("-",$process_month ?? "");
}
$process_month_val = (int)$process_month[0];
if($esi_limit > 0){
if(($esi_start_check_month === $process_month_val) || ($esi_end_check_month === $process_month_val)){
$upd_query = 'UPDATE cw_employees SET esi_eligibility = CASE WHEN esi_location = 1 THEN 2 WHEN '.$formula.' <= '.$esi_limit.' THEN 1 ELSE 2 END WHERE trans_status = 1 and role = "'.$category.'"';
$update_info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
if(count($middle_inc ?? []) > 0){
$employee_codes = implode(",",array_column($middle_inc ?? [], 'employee_code') ?? []);
//Check increment value based on esi limit
$upd_query = 'UPDATE cw_employees inner join cw_arrear_cumulative on cw_arrear_cumulative.employee_code = cw_employees.employee_code SET esi_eligibility = CASE WHEN esi_location = 1 THEN 2 WHEN '.$formula.'+arrear_value > '.$esi_limit.' THEN 2 ELSE 1 END WHERE trans_status = 1 and role = "'.$category.'" and arrear_column = "'.$arr_gross_col.'" and cw_employees.employee_code in ('.$employee_codes.')';
$update_info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
}
}else{
//Check missing employees and new joiners
$upd_query = 'UPDATE cw_employees SET esi_eligibility = CASE WHEN esi_location = 1 THEN 2 WHEN '.$formula.' <= '.$esi_limit.' THEN 1 ELSE 2 END WHERE trans_status = 1 and role = "'.$category.'" and esi_eligibility in (0,2)';
$update_info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
}
}
}
}
}
?>