File: /home/cafsindia/cloud_cafsinfotech_in/application/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');
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);
}
//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";
}
$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;
}
}
//print_r($final_pick); die;
$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');
$final_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 and employee_code like "'.$search_term.'%"';
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$suggestions[] = array('value' => $employee_code, 'label' => "$employee_code - $emp_name");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
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 = array_filter($this->input->post('process_role'));
$process_role = implode(",",$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);
}
//payroll function mapping Check
$pt_amount_qry = 'SELECT count(*) as pt_amount_count FROM cw_payroll_function_map WHERE loc_name ="professional_tax_amount" 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 === 0){
echo json_encode(array('success' => false, 'message' => 'Please add payroll function mapping for PT Amount 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);
}
//fandf checking for employee updated
/*$fandf_check_qry = 'select count(*) as fandf_count from cw_employees where trans_status = 1 and employee_code = "'.$process_emp_code.'" and termination_status =1 and trans_status =1';
$fandf_check_data = $this->db->query("CALL sp_a_run ('SELECT','$fandf_check_qry')");
$fandf_check_result = $fandf_check_data->result();
$fandf_check_data->next_result();
$fandf_check_count = (int)$fandf_check_result[0]->fandf_count;*/
$where_query = "";
$cond_query = "";
$where_inc = "";
$where_loan = "";
if($process_type === 1){
$where_query = ' and employee_code = "'.$process_emp_code.'" ';
$cond_query = ' and cw_fandf_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_fandf_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_fandf_monthly_input where trans_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
$check_monthly_input_qry = 'select count(*) as result_count from cw_fandf_monthly_input where trans_status = 1 and input_status = 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' => "FandF Monthly Input Not Generated?"));
exit(0);
}
//check
if((int)$process_mode === 1){
//check monthly data is present or not
/*$check_monthly_input_qry = 'select count(*) as result_count from cw_fandf_monthly_input where trans_status = 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);*/
//}else{
//Already payroll processed or not?
$istrans_exist_qry = 'select count(*) as result_count from cw_transactions where trans_status = 1 and cw_transactions.termination_status = 1 and transactions_month = "'.$process_month.'" and fandf = 1 '.$where_query;
// echo "$istrans_exist_qry";die;
$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));
exit(0);
}else{
// EMPLOYEE AND MONTHLY INPUT COLUMN
$column_qry = 'SELECT prime_form_id,GROUP_CONCAT(CASE WHEN earn_month_check = "1" THEN CONCAT("cw_fandf_monthly_input.",label_name," as ",label_name) WHEN deduction_month_check="1" THEN CONCAT("cw_fandf_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;
//Loan Process END*/
//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 = 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 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.' group by cw_loan_installment.emp_code,cw_loan_installment.loan_type';
//echo $paid_qry; die;
$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 where install_year = "'.$process_month.'" '.$where_loan.' 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) . '"';
//and install_year ="'.$process_month.'"
$process_dt = date("Y-m-d",strtotime("01-$process_month"));
$upd_query = 'UPDATE cw_loan_installment SET paid_status = 1 where emp_code in ('.$imp.') and trans_status=1 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 and category ="2"';
$month_day_data = $this->db->query("CALL sp_a_run ('SELECT','$month_day_qry')");
$month_day_result = $month_day_data->result();
$month_day_data->next_result();
//Get Salary Start Date
if($month_day_result){
$role = $month_day_result[0]->category;
$day_conditions = $month_day_result[0]->day_conditions;
$day_count = $month_day_result[0]->day_count;
$day_start = $month_day_result[0]->day_start;
$day_end = $month_day_result[0]->day_end;
if((int)$day_conditions === 3){
$sal_start = $day_start;
//For Current month between days increment
$date = new DateTime("01-$process_month 00:00:00");
$date->modify('-1 month');
$salary_start_date = $date->format("Y-m-$sal_start");
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}else{
$sal_start = '01';
$date = new DateTime("01-$process_month 00:00:00");
$salary_start_date = $date->format("Y-m-$sal_start");
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}
}
$emp_info_query = 'select "1" as fandf,cw_employees.prime_employees_id as employees_id,'.$column_label.' from cw_employees
inner join cw_fandf_monthly_input on cw_employees.employee_code = cw_fandf_monthly_input.employee_code where cw_employees.trans_status = 1 and cw_fandf_monthly_input.termination_status = 1 and cw_employees.payroll = 1 and cw_employees.role != 1 and cw_fandf_monthly_input.trans_status = 1 and cw_fandf_monthly_input.supplementary_status = 0 and input_status = 1 and cw_fandf_monthly_input.process_month = "'.$process_month.'"'.$cond_query;
//.' order by cw_employees.prime_employees_id limit 0,1000'
$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
if($loan_array){
$trans_array = array_replace_recursive($trans_array, $loan_array);
}
/*//Increment Data Array Merge
if($increment_array){
$trans_array = array_replace_recursive($trans_array, $increment_array);
}*/
//print_r($increment_array); die;
//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);
$table_view = $this->transaction_data($process_month,$process_role,$process_emp_code);
$mi_upd_query = 'UPDATE cw_fandf_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 '.$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){
$logged_id = $this->session->userdata('logged_id');
$employee_code = $increment_check_result[0]->employee_code;
$effective_date = $increment_check_result[0]->effective_date;
$eff_month = date("m-Y",strtotime($effective_date));
if($process_type === 1){
$where_inc = ' and employee_code = "'.$process_emp_code.'" ';
}else
if($process_type === 2){
$where_inc = 'and cw_increment_approval.category = "'.$process_role.'"';
}else{
$where_inc = "";
}
$logged_id = $this->session->userdata('logged_id');
//Loan Update paid Status
$process_dt = date("Y-m-d",strtotime("01-$process_month"));
$upd_query = 'UPDATE cw_loan_installment SET paid_status = 0 where date_format(str_to_date(CONCAT("01-", install_year), "%d-%m-%Y") , "%Y-%m-%d") >= "'.$process_dt.'" and trans_status = 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 transactions_month = "' . $process_month . '" and termination_status = 1 and fandf = 1 ' . $where_query;
//echo "BSK $process_payroll_query"; die;
$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 transactions_month = "' . $process_month . '" and termination_status = 1 and fandf = 1 ' . $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 . '" and termination_status = 1 and fandf = 1 ' . $where_query;
$this->db->query("CALL sp_a_run ('RUN','$process_payroll_upd_query')");
}
//update mi
$mi_upd_query = 'UPDATE cw_fandf_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 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 DISTINCT(cw_increment.employee_code) as employee_code,GROUP_CONCAT(column_name) as column_names,GROUP_CONCAT(current_value) as current_values FROM `cw_increment` inner join cw_increment_approval on cw_increment_approval.prime_increment_approval_id = cw_increment.increment_approval_id where cw_increment_approval.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();
foreach($inc_columns_result as $key => $inc_rslt){
$column_names = explode(",",$inc_rslt->column_names);
$current_values = explode(",",$inc_rslt->current_values);
$arrear_data = array_combine($column_names,$current_values);
$increment_array[$inc_rslt->employee_code] = $arrear_data;
}
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')");
}
}
}
}
echo json_encode(array('success' => TRUE,'message' =>"Payroll Processed Deleted!!! ",'table_content' =>$table_content));
}else{
$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));
}
}
//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;
$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)$tot_count > 0 || (int)$loan_tot_count > 0 || (int)$earn_ded_count > 0){
$can_process = true;
}else{
$can_process = false;
}
}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();
//print_r($earn_ded_rslt); die;
$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){
$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('fandf,employees_id,transactions_month,total_earnings,total_deductions,net_pay,trans_created_by,trans_created_date,'.$query_key,",");
$query_value = rtrim('\"$logged_id\",\"$date\",'.$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["fandf"]."'."','".'".$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 = "";
//role wise query execution
foreach ($role_array as $key => $role_value){
$qry_build .= "\n\t if(\$payroll_array[".$role_value."]){ \n\t \$this->db->query(\$qry_".$role_value."); \n\t }";
}
//$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");';
$fname = 'Payroll_calculation($trans_array){';
$final_code = "<?php\n class Fandf_calculation_model extends CI_Model{
private \$payroll_count;
\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\tprivate \$previous_paid;
\n\tprivate \$pending_slry;
\n\tprivate \$sep_day_res;
\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
\$sep_day_qry = 'select employee_code,last_working_date from cw_employees where trans_status = 1 and termination_status =1';
\$sep_day_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$sep_day_qry')\");
\$sep_day_result = \$sep_day_data->result_array();
\$sep_day_data->next_result();
\$this->sep_day_res = array_reduce(\$sep_day_result, function (\$result, \$arr){
\$result[\$arr['employee_code']] = \$arr['last_working_date'];
return \$result;
}, array());
//get Previous month
\$previous_paid_qry = 'select employee_code,date_format(MAX(str_to_date(CONCAT(\"01-\", transactions_month), \"%d-%m-%Y\")) , \"%m-%Y\") as transactions_month from cw_transactions where trans_status = 1 and stop_pay_status = 0 GROUP BY employee_code';
\$previous_paid_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$previous_paid_qry')\");
\$previous_paid_rslt = \$previous_paid_data->result_array();
\$previous_paid_data->next_result();
\$this->previous_paid = array_reduce(\$previous_paid_rslt, function (\$result, \$arr){
\$result[\$arr['employee_code']] = \$arr['transactions_month'];
return \$result;
}, array());
\$pending_slry_qry = 'select employee_code,transactions_month,net_pay from cw_transactions where trans_status = 1 and stop_pay_status = 1 GROUP BY employee_code,transactions_month';
\$pending_slry_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$pending_slry_qry')\");
\$pending_slry_rslt = \$pending_slry_data->result_array();
\$pending_slry_data->next_result();
\$this->pending_slry = array_reduce(\$pending_slry_rslt, function (\$result, \$arr){
\$result[\$arr['employee_code']][\$arr['transactions_month']] = \$arr;
return \$result;
}, array());
//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();
\$this->payroll_count = array_reduce(\$payroll_exist_result, function (\$result, \$arr){
\$result[\$arr['employee_code']] = \$arr['pay_count'];
return \$result;
}, array());\n\t
//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 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();
\$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();
\$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 $final_code $qry_build return true; \n\t}\n\n\tpublic function rounding_value(\$result, \$round_mode){\n\t\t\$result = round(\$result,2);\n\t\t\$final_result =0;\n\t\tif((\$round_mode == 0.5) || (\$round_mode == 1)){\n\t\t\t\$final_result = round(\$result/\$round_mode, 0)* \$round_mode;\n\t\t}elseif(\$round_mode == '>1'){\n\t\t\t\$final_result = ceil(\$result);\n\t\t}elseif(\$round_mode == '<1'){\n\t\t\t\$final_result = floor(\$result);\n\t\t}elseif(\$round_mode == '<0.5'){\n\t\t\t\$rslt = explode('.', \$result);\n\t\t\t\$int_value = \$rslt[0];\n\t\t\t\$point_value = \$rslt[1];\n\t\t\tif((int)\$point_value > 50){\n\t\t\t\t\$final_result = \$int_value.'.50';\n\t\t\t}else{\n\t\t\t\t\$final_result = ceil(\$result);\n\t\t\t}\n\t\t}elseif(\$round_mode == '>0.5'){\n\t\t\t\$rslt = explode('.', \$result);\n\t\t\t\$int_value = \$rslt[0];\n\t\t\t\$point_value = \$rslt[1];\n\t\t\tif((int)\$point_value >= 50){\n\t\t\t\t\$final_result = round(\$result);\n\t\t\t}else{\n\t\t\t\t\$final_result = \$int_value.'.50';\n\t\t\t}\n\t\t}elseif(((int)\$round_mode == 5) || ((int)\$round_mode == 10) || ((int)\$round_mode == 50) || ((int)\$round_mode == 100)){\n\t\t\t\$final_result = (ceil(\$result)% \$round_mode === 0) ? ceil(\$result) : round((\$result+ \$round_mode/2)/ \$round_mode)*\$round_mode;\n\t\t}elseif(\$round_mode == 0.1){\n\t\t\t\$final_result = round(\$result, 2);\n\t\t}\n\t\treturn \$final_result;\n\t}
\n\tpublic function dz(\$result){\n\t\tif(is_nan(\$result) || is_infinite(\$result)){\n\t\t\t\$result = 0;\n\t\t}else{\n\t\t\t\$result;\n\t\t}\n\t\treturn \$result;\n\t} //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 Documentation Fee
public function get_document_fees(\$category,\$employee_code,\$process_month){
\$payroll_exist_count = \$this->payroll_count[\$employee_code];
if((int)\$payroll_exist_count === 0 && \$process_month !== \"03-2020\"){
return 500;
}else{
return 0;
}
}
//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];
\$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;
}
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'];
//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();
if(!empty(\$select_loc_result)){
\$esi_location = \$select_loc_result[0]->esi_loc;
if((int)\$esi_location){
\$upd_query = 'UPDATE cw_employees SET '.\$esi_elig_db.' = 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_elig_db.' = 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){
\$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;
}
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'];
\$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, '.', '');
//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{
\$gratuity_amt = 0;
return \$gratutity_amt;
}
}
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);
}
}
}
public function get_fandf_professional_tax_value(\$professional_tax_location,\$pt_projection,\$total_earnings,\$process_month){
\$professional_tax_amount_db = \$this->get_map_arr['professional_tax_amount']['db_column'];
\$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 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 and location = \"'.\$professional_tax_location.'\"';
\$ptax_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$ptax_qry')\");
\$ptax_result = \$ptax_data->result();
\$ptax_data->next_result();
\$prof_tax_id = \$ptax_result[0]->prime_professional_tax_id;
\$location = \$ptax_result[0]->location;
\$calculation_mode = \$ptax_result[0]->calculation_period;
\$ptax_first_period = \$ptax_result[0]->osm_first_end;
\$ptax_second_period = \$ptax_result[0]->osm_second_end;
\$ptax_deduct_first = \$ptax_result[0]->first_period;
\$ptax_deduct_second = \$ptax_result[0]->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\";
//tax area Tamil Nadu tax calculation
//By Formula every month calculated ptax amount based on earn gross
\$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){
//By Monthly -- Second Method -- values
//create first and second tax pay period array
\$period_qry = 'select osm_first_start,osm_first_end,osm_second_start,osm_second_end from cw_professional_tax where trans_status =1 and location = \"'.\$professional_tax_location.'\"';
\$period_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$period_qry')\");
\$period_result = \$period_data->result();
\$period_data->next_result();
\$osm_first_st_val = \$period_result[0]->osm_first_start;
\$osm_first_ed_val = \$period_result[0]->osm_first_end;
\$osm_second_st_val = \$period_result[0]->osm_second_start;
\$osm_second_ed_val = \$period_result[0]->osm_second_end;
\$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;
}
\$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);
\$loop_count = 5;
\$tot_count = 0;
//calculating pending projection count loop
if(in_array(\$process_month_val,\$first_period)){
\$cur_count = array_search(\$process_month_val, array_values(\$first_period));
\$tot_count = (int)\$loop_count - (int)\$cur_count;
}else
if(in_array(\$process_month_val,\$second_period)){
\$cur_count = array_search(\$process_month_val, array_values(\$second_period));
\$tot_count = (int)\$loop_count - (int)\$cur_count;
}
//projection amount
\$projection_total = \$pt_projection * (int)\$tot_count; //2 month count
//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(total_earnings),0) as total_earnings from cw_transactions where trans_status =1 and employee_code = \"'.\$employee_code.'\" and date_format(str_to_date(transactions_month, \"%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;
//loop processing count
\$process_count = (int)\$tot_count + 1;
//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(transactions_month, \"%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
\$period_qry = 'select osm_first_start,osm_first_end,osm_second_start,osm_second_end from cw_professional_tax where trans_status =1 and location = \"'.\$professional_tax_location.'\" ';
\$period_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$period_qry')\");
\$period_result = \$period_data->result();
\$period_data->next_result();
\$osm_first_st_val = \$period_result[0]->osm_first_start;
\$osm_first_ed_val = \$period_result[0]->osm_first_end;
\$osm_second_st_val = \$period_result[0]->osm_second_start;
\$osm_second_ed_val = \$period_result[0]->osm_second_end;
\$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;
}
\$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);
\$loop_count = 5;
\$tot_count = 0;
//calculating pending projection count loop
if(in_array(\$process_month_val,\$first_period)){
\$cur_count = array_search(\$process_month_val, array_values(\$first_period));
\$tot_count = (int)\$loop_count - (int)\$cur_count;
}else
if(in_array(\$process_month_val,\$second_period)){
\$cur_count = array_search(\$process_month_val, array_values(\$second_period));
\$tot_count = (int)\$loop_count - (int)\$cur_count;
}
//Find Projection amount
//projection amount
\$projection_total = \$pt_projection * (int)\$tot_count; //2 month count
//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(total_earnings),0) as total_earnings from cw_transactions where trans_status =1 and employee_code = \"'.\$employee_code.'\" and date_format(str_to_date(transactions_month, \"%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;
}
return \$this->rounding_value(\$prof_tax,1);
}
//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'];
/*if(\$term_sts === 1){
\$prof_tax = \$this->get_fandf_professional_tax_value(\$employee_code,\$professional_tax_location,\$pt_projection,\$total_earnings,\$process_month);
}else{*/
\$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_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 and location = \"'.\$professional_tax_location.'\"';
\$ptax_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$ptax_qry')\");
\$ptax_result = \$ptax_data->result();
\$ptax_data->next_result();
\$prof_tax_id = \$ptax_result[0]->prime_professional_tax_id;
\$location = \$ptax_result[0]->location;
\$calculation_mode = \$ptax_result[0]->calculation_period;
\$osm_first_st_val = \$ptax_result[0]->osm_first_start;
\$osm_first_ed_val = \$ptax_result[0]->osm_first_end;
\$osm_second_st_val = \$ptax_result[0]->osm_second_start;
\$osm_second_ed_val = \$ptax_result[0]->osm_second_end;
\$osm_second_end_val = \$ptax_result[0]->osm_second_end;
\$ptax_deduct_first = \$ptax_result[0]->first_period;
\$ptax_deduct_second = \$ptax_result[0]->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
\$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;
//\$prof_tax = \$prof_tax_amt/\$process_count;
}else
if((int)\$calculation_mode === 3){
//By Once in six month -- Last Method
if(((int)\$process_month_val === (int)\$ptax_deduct_first) || ((int)\$process_month_val === (int)\$ptax_deduct_second)){
//Find Projection amount
//projection amount
\$projection_total = \$pt_projection * (int)\$proj_count; //2 month count
//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;
}
//}
}
return \$this->rounding_value(\$prof_tax, 1);
}
public function get_pending_salary(\$employee_code,\$transactions_month){
\$last_paid_month = \$this->previous_paid[\$employee_code];
\$pending_slry = \$this->pending_slry[\$employee_code];
if(\$last_paid_month && \$transactions_month){
\$get_last_paid_date = \$this->get_months(\$last_paid_month,\$transactions_month);
\$sum_pending_slry = 0;
foreach (\$get_last_paid_date as \$dt){
\$month = \$dt->format(\"m-Y\");
\$sum_pending_slry += \$pending_slry[\$month]['net_pay'];
}
return \$sum_pending_slry;
}else{
return 0;
}
}
//Get all the months from the effective date.
public function get_months(\$last_paid_month,\$transactions_month){
\$start = new DateTime(\"01-\$last_paid_month 00:00:00\");
\$start->modify('first day of this month');
\$end = new DateTime(\"01-\$transactions_month 00:00:00\");
\$end->modify('first day of this month');
\$interval = DateInterval::createFromDateString('1 month');
\$period = new DatePeriod(\$start, \$interval, \$end);
return \$period;
}
\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 fandf = 1";
if($process_role){
$search_val = ' and cw_transactions.role in('.$process_role.') and cw_transactions.termination_status = 1';
}else
if($process_emp_code){
$search_val = ' and cw_transactions.employee_code = "'.$process_emp_code.'" and cw_transactions.termination_status = 1';
}
$select_query = "";
$pick_query = "";
$final_qry = "";
$thead_line = "";
$thead = "";
$form_qry = 'SELECT prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "employees" and transaction_type in (1,2,3) and (earn_payroll_check = "1" or ded_payroll_check = "1") and trans_status = "1" order by payroll_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
$loan_column_qry = 'SELECT GROUP_CONCAT(label_name) as loan_columns FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND input_view_type IN (1,2) and loan_check = 1 ORDER BY prime_form_id';
$loan_column_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_column_qry')");
$loan_column_rslt = $loan_column_data->result();
$loan_column_data->next_result();
$loan_columns = array();
if($loan_column_rslt[0]->loan_columns){
$loan_columns = explode(",",$loan_column_rslt[0]->loan_columns);
}
$query_key = "";
$loan_array = array();
foreach ($loan_columns as $key => $loan_column){
//Generate Query Key For Loan
$query_key .= ",cw_transactions.".$loan_column."_total";
$query_key .= ",cw_transactions.".$loan_column."_installments";
$query_key .= ",cw_transactions.".$loan_column."_instal_count";
$query_key .= ",cw_transactions.".$loan_column."_balance";
$loan_array[] = $loan_column."_total";
$loan_array[] = $loan_column."_installments";
$loan_array[] = $loan_column."_instal_count";
$loan_array[] = $loan_column."_balance";
}
$loan_thead = "";
foreach($loan_array as $loan){
$loan = str_replace("_", " ", $loan);
$loan = ucwords($loan);
$loan_thead .= "<th>$loan</th>";
}
$table_name = "cw_transactions";
$i = 1;
//echo "<pre>";
//print_r($form_result); die;
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
if($label_name == "role"){
$view_name = "Category";
}
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else{
$select_query .= "$table_name.$label_name , ";
}
if((int)$i === 1){
$th_style = "class='hard_left'";
}else
if((int)$i === 2){
$th_style = "class='next_left'";
}else{
$th_style = "";
}
$thead_line .= "<th $th_style>$view_name</th>";
$i++;
}
$thead = "<tr>$thead_line $loan_thead</tr>";
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
//AS Discussed with collman uday cw_transactions.stop_pay_status !=1 and removed
$trans_status = " left join cw_employees on cw_employees.employee_code =cw_transactions.employee_code where ".$table_name.".trans_status = 1 and transactions_month=\"".$process_month."\" and cw_transactions.termination_status = 1";
$final_qry = "select $select_query $query_key from $table_name $pick_query $trans_status $search_val";
//echo $final_qry; die;
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
$tr_line = "";
foreach($final_result as $rslt){
$td_line = "";
$j = 1;
foreach($rslt as $value){
if((int)$j === 1){
$cls = "class='hard_left'";
}else
if((int)$j === 2){
$cls = "class='next_left'";
}else{
$cls = "";
}
$td_line .= "<td $cls>$value</td>";
$j++;
}
$tr_line .= "<tr>$td_line</tr>";
}
$table_content = "<div class='outer'><div class='inner' ><table class='table table-bordered col-style' id='detail_list' style='box-shadow:none;'>
<thead>
$thead
</thead>
<tbody>
$tr_line
</tbody>
</table></div></div>";
return $table_content;
//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 = PHPExcel_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));
}
//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 = 1';
$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 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 = PHPExcel_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();
for($row =$excel_row_start; $row <= $total_rows; $row++){
$prime_column_val = "";
$prime_cell_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.'",';
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]->resignation_date;
$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_employees where cw_employees.trans_status =1 $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 === 1){
$prime_column_val .= "employees_id,emp_name,resignation_date,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";
}
$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"));
}
}
}
}
}
//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 ="employees" AND cond_drop_down ="role" AND line_input_bind_table = "'.$role.'" 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));
}
//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 margin_relief_amt($earn_value,$total_tax){
$margin_relief_qry = 'select * from cw_surcharge_slab where income_start_amount <="'.$earn_value.'" and income_end_amount >="'.$earn_value.'"';
$margin_relief_data = $this->db->query("CALL sp_a_run ('SELECT','$margin_relief_qry')");
$margin_relief_result = $margin_relief_data->result();
$margin_relief_data->next_result();
$margin_relief_arr = array();
if($margin_relief_result){
$surcharge_pct = $margin_relief_result[0]->surcharge_percentage;
if((int)$surcharge_pct !== 0){
$slab_start_amt = $margin_relief_result[0]->income_start_amount;
$slab_start_amt_pct = $slab_start_amt - 1;
$inc_surcharge_tax = round($total_tax * ($surcharge_pct/100));
$income_excess_amt = $earn_value - $slab_start_amt_pct;
//income excess amount default tax calculation is 30 percentage
$income_excess_amt_tax = $income_excess_amt * (30/100);
$income_tax_surch_amt = $inc_surcharge_tax + $income_excess_amt_tax;
$margin_relief = round($income_tax_surch_amt - $income_excess_amt);
if($margin_relief < 0){
$margin_relief = 0;
}
}else{
$margin_relief = 0;
$inc_surcharge_tax = 0;
}
$margin_relief_arr = array('surcharge_pct'=>$surcharge_pct,'surcharge_charge'=>$inc_surcharge_tax,'margin_relief'=>$margin_relief);
}
return $margin_relief_arr;
}
public function get_pt_projection($pt_proj_amt,$tax_location,$process_month,$count,$employee_code,$professional_tax_db){
$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 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 and location ='.$tax_location;
$ptax_data = $this->db->query("CALL sp_a_run ('SELECT','$ptax_qry')");
$ptax_result = $ptax_data->result();
$ptax_data->next_result();
$prof_tax_id = $ptax_result[0]->prime_professional_tax_id;
$location = $ptax_result[0]->location;
$calculation_mode = $ptax_result[0]->calculation_period;
$ptax_first_period = $ptax_result[0]->osm_first_end;
$ptax_second_period = $ptax_result[0]->osm_second_end;
$ptax_deduct_first = $ptax_result[0]->first_period;
$ptax_deduct_second = $ptax_result[0]->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";
//tax area Tamil Nadu tax calculation
//By Formula every month calculated ptax amount based on earn gross
$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($pt_proj_amt).'" and earning_range_to >= '.floor($pt_proj_amt);
$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)*$count;
}else
if((int)$calculation_mode === 2){
//By Monthly -- Second Method -- values
//create first and second tax pay period array
$period_qry = 'select osm_first_start,osm_first_end,osm_second_start,osm_second_end from cw_professional_tax where trans_status =1 and location ="'.$tax_location.'"';
$period_data = $this->db->query("CALL sp_a_run ('SELECT','$period_qry')");
$period_result = $period_data->result();
$period_data->next_result();
$osm_first_st_val = $period_result[0]->osm_first_start;
$osm_first_ed_val = $period_result[0]->osm_first_end;
$osm_second_st_val = $period_result[0]->osm_second_start;
$osm_second_ed_val = $period_result[0]->osm_second_end;
$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;
}
$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);
$loop_count = 5;
$tot_count = 0;
//calculating pending projection count loop
if(in_array($process_month_val,$first_period)){
$cur_count = array_search($process_month_val, array_values($first_period));
$tot_count = (int)$loop_count - (int)$cur_count;
}else
if(in_array($process_month_val,$second_period)){
$cur_count = array_search($process_month_val, array_values($second_period));
$tot_count = (int)$loop_count - (int)$cur_count;
}
//projection amount
$projection_total = (int)$pt_proj_amt * (int)$tot_count; //2 month count
//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(total_earnings),0) as total_earnings from cw_transactions where trans_status =1 and employee_code = "'.$employee_code.'" and date_format(str_to_date(transactions_month, "%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 = (int)$previous_earned + (int)$projection_total;
//loop processing count
$process_count = (int)$tot_count;
//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_db.'),0) as professional_tax from cw_transactions where trans_status =1 and employee_code = "'.$employee_code.'" and date_format(str_to_date(transactions_month, "%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_amt = $find_tax_val - $last_tax_val;
if($count > 6){
$pending_tax_val = $find_tax_result[0]->ptax_amt;
$prof_tax = $pending_tax_val + $prof_tax_amt;
}else{
$prof_tax = ($prof_tax_amt/$process_count)*$count;
}
}else
if((int)$calculation_mode === 3){
//By Once in six month -- Last Method
if(((int)$process_month_val === (int)$ptax_deduct_first) || ((int)$process_month_val === (int)$ptax_deduct_second)){
$period_qry = 'select osm_first_start,osm_first_end,osm_second_start,osm_second_end from cw_professional_tax where trans_status =1 and location ="'.$tax_location.'" ';
$period_data = $this->db->query("CALL sp_a_run ('SELECT','$period_qry')");
$period_result = $period_data->result();
$period_data->next_result();
$osm_first_st_val = $period_result[0]->osm_first_start;
$osm_first_ed_val = $period_result[0]->osm_first_end;
$osm_second_st_val = $period_result[0]->osm_second_start;
$osm_second_ed_val = $period_result[0]->osm_second_end;
$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;
}
$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);
$loop_count = 5;
$tot_count = 0;
//calculating pending projection count loop
if(in_array($process_month_val,$first_period)){
$cur_count = array_search($process_month_val, array_values($first_period));
$tot_count = (int)$loop_count - (int)$cur_count;
}else
if(in_array($process_month_val,$second_period)){
$cur_count = array_search($process_month_val, array_values($second_period));
$tot_count = (int)$loop_count - (int)$cur_count;
}
//Find Projection amount
//projection amount
$projection_total = (int)$pt_proj * (int)$tot_count; //2 month count
//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(total_earnings),0) as total_earnings from cw_transactions where trans_status =1 and employee_code = "'.$employee_code.'" and date_format(str_to_date(transactions_month, "%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 = (int)$previous_earned + (int)$projection_total; // 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;
}
}
return round($prof_tax);
}
public function get_months($start_date,$end_date){
$start = new DateTime($start_date);
$end = new DateTime($end_date);
$start->modify('first day of this month');
$end->modify('last day of this month');
$interval = DateInterval::createFromDateString('1 month');
$period = new DatePeriod($start, $interval, $end);
return $period;
}
public function month_count($process_date){
$end_date = $this->financial_info[0]->end_date;
$process_date = strtotime($process_date);
$end_date = strtotime($end_date);
$process_year = date('Y', $process_date);
$end_year = date('Y', $end_date);
$process_month = date('m', $process_date);
$end_month = date('m', $end_date);
$diff = (($end_year - $process_year) * 12) + ($end_month - $process_month) + 1;
return $diff;
}
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?";
}
//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";
}
return $err_info;
}
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>";
}
$tr_line .="<tr><td colspan='2'>Please do default setting for first!!!</td></tr>";
$table_info = "<table class='table table-bordered'>
<thead>
<tr>
<th>Hints</th>
<th>Error Details</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $table_info;
}
}
?>