File: /home/cafsindia/uds.cafsinfotech.in/Dump/ZRM/application/controllers/Process_payroll_bk.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 Process_payroll extends Secure_Controller{
//public $payroll_process_type = "";
public function __construct(){
parent::__construct('process_payroll');
$this->create_formula_file();
$this->inc_create_formula_file();
// $this->load->model('Process_payroll_model');
$this->load->model('Hr_methods_model');
$this->load->model('Payroll_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 = implode(",",$this->input->post('process_role'));
$process_income_tax = $this->input->post('process_income_tax');
$process_proof_wise = $this->input->post('process_proof_wise');
$process_mode = $this->input->post('process_mode');
$process_income_tax_val = 0;
$process_proof_wise_val = 0;
$reprocess_val = 0;
if($process_income_tax === "on"){ $process_income_tax_val = 1; }
if($process_proof_wise === "on"){ $process_proof_wise_val = 1; }
$process_month = $process_month[0]."-".$process_month[1];
//payroll function mapping Check
$function_qry = 'SELECT count(*) as count FROM cw_payroll_function WHERE statutory_name in (3,8) and function_name in (3,5) and trans_status = 1';
$function_info = $this->db->query("CALL sp_a_run ('SELECT','$function_qry')");
$function_rslt = $function_info->result();
$function_info->next_result();
$function_count = $function_rslt[0]->count;
if((int)$function_count < 2){
echo json_encode(array('success' => false, 'message' => 'Please add payroll function for PT Amount and Month Days...'));
exit(0);
}
//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);
}
$where_query = "";
$cond_query = "";
$where_inc = "";
$where_loan = "";
if($process_type === 1){
$where_query = ' and employee_code = "'.$process_emp_code.'" ';
$cond_query = ' and cw_monthly_input.employee_code = "'.$process_emp_code.'" ';
$where_inc = ' and employee_code = "'.$process_emp_code.'" ';
$where_loan = ' and cw_loan_installment.emp_code = "'.$process_emp_code.'" ';
}else
if($process_type === 2){
$where_query = ' and '.$this->payroll_process_type.' in ('.$process_role.')';
$cond_query = ' and cw_monthly_input.'.$this->payroll_process_type.' in ('.$process_role.')';
//Get Category details for Loan and Increment
$get_role_qry = 'select GROUP_CONCAT(DISTINCT role) as roles from cw_monthly_input where trans_status = 1 and 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_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' => "Monthly Input Not Generated?"));
exit(0);
}
//check
if((int)$process_mode === 1){
/*if((int)$fandf_check_count === 1){
echo json_encode(array('success' => false, 'message' => "Already employee is resigned, not possible to process payroll?"));
exit(0);
}*/
//else{
//Already payroll processed or not?
$istrans_exist_qry = 'select count(*) as result_count from cw_transactions where trans_status = 1 and transactions_month = "'.$process_month.'"'.$where_query;
$istrans_data = $this->db->query("CALL sp_a_run ('SELECT','$istrans_exist_qry')");
$istrans_result = $istrans_data->result();
$istrans_data->next_result();
$istrans_count = (int)$istrans_result[0]->result_count;
if($istrans_count > 0){
$table_view = $this->transaction_data($process_month,$process_role,$process_emp_code);
echo json_encode(array('success' => false, 'message' => "Already exist in transaction. Please remove transaction!",'table_content' =>$table_view));
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_monthly_input.",label_name," as ",label_name) WHEN deduction_month_check="1" THEN CONCAT("cw_monthly_input.",label_name," as ",label_name) ELSE CONCAT("cw_employees.",label_name," as ",label_name) END ORDER BY 1 SEPARATOR ",") AS columns FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND input_view_type IN (1,2) ORDER BY prime_form_id';
$column_data = $this->db->query("CALL sp_a_run ('SELECT','$column_qry')");
$column_rslt = $column_data->result();
$column_data->next_result();
$column_label = $column_rslt[0]->columns;
//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 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 '.$where_loan;
$paid_data = $this->db->query("CALL sp_a_run ('SELECT','$paid_qry')");
$paid_result = $paid_data->result_array();
$paid_data->next_result();
$result = array_map(function($v){
return [$v['emp_code'] => $v['paid']];
}, $paid_result);
//Convert Multi dimentional array to single array
$loan_paid = call_user_func_array('array_merge', $result);
//Get Loan Details
$loan_qry = 'SELECT cw_loan_installment.emp_code,cw_loan_installment.loan_type,IFNULL(sum(cw_loan_installment.loan_amount),0) as loan_amount,IFNULL(sum(cw_loan_installment.number_of_installment),0) as number_of_installment,IFNULL(sum(cw_loan_installment.install_amount),0) as install_amount,IFNULL(sum(cw_loan_installment.installment_count),0) as installment_count,cw_loan_installment.category FROM `cw_loan_installment`inner join cw_loan on cw_loan.prime_loan_id = cw_loan_installment.loan_id inner join cw_monthly_input on cw_monthly_input.employee_code = cw_loan_installment.emp_code where cw_loan.trans_status = 1 and cw_loan.foreclose_sts = 0 and cw_loan_installment.trans_status=1 and install_year = "'.$process_month.'" '.$where_loan.' and cw_monthly_input.trans_status = 1 and cw_monthly_input.termination_status = 0 and cw_monthly_input.payroll = 1 and cw_monthly_input.supplementary_status = 0 and cw_monthly_input.process_month = "'.$process_month.'" '.$cond_query.' 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] = $loan_rslt[$loan_column]['install_amount'];
$loan_array[$emp_code][$loan_column.'_balance'] = $loan_rslt[$loan_column]['loan_amount'] - ($loan_paid[$emp_code] + $loan_rslt[$loan_column]['install_amount']);
}
$loan_employees[] = $emp_code;
}
if(count($loan_employees) > 0){
$loan_employees = implode(",",$loan_employees);
$loan_employees = str_replace(",",'","',$loan_employees);
$loan_employees = '"'.$loan_employees.'"';
$upd_query = 'UPDATE cw_loan_installment SET paid_status = 1 where emp_code in ('.$loan_employees.') and install_year ="'.$process_month.'" and trans_status=1';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
}
//Loan Process END
//Salary Date
$month_day_qry = 'select category,day_conditions,day_count,day_start,day_end from cw_month_day where cw_month_day.trans_status = 1';
$month_day_data = $this->db->query("CALL sp_a_run ('SELECT','$month_day_qry')");
$month_day_result = $month_day_data->result_array();
$month_day_data->next_result();
$month_day_result = array_reduce($month_day_result, function ($result, $arr) {
$result[$arr['category']] = $arr;
return $result;
}, array());
$emp_info_query = 'select cw_employees.prime_employees_id as employees_id,'.$column_label.' from cw_employees
inner join cw_monthly_input on cw_employees.employee_code = cw_monthly_input.employee_code where cw_employees.trans_status = 1 and cw_employees.payroll = 1 and cw_employees.role != 1 and cw_monthly_input.trans_status = 1 and cw_monthly_input.supplementary_status = 0 and input_status = 1 and cw_monthly_input.process_month = "'.$process_month.'"'.$cond_query;
echo "BSK $emp_info_query"; die;
$company_info = $this->Module->get_company_info();
$increment_type = $company_info[0]->increment_type;
if((int)$increment_type === 1){
$increment_check_qry = 'SELECT GROUP_CONCAT(DISTINCT(employee_code) ORDER BY employee_code asc) as employee_code from cw_increment inner join cw_increment_approval on cw_increment_approval.prime_increment_approval_id = cw_increment.increment_approval_id where apply_month="'.$process_month.'" '.$where_inc.' and cw_increment_approval.trans_status = 1 and hr_status = 2';
}else
if((int)$increment_type === 2){
$increment_check_qry = 'select GROUP_CONCAT(DISTINCT(employee_code) ORDER BY employee_code asc) as employee_code from cw_increment where apply_on="'.$process_month.'" '.$where_inc.' and trans_status = 1';
}
$increment_check_info = $this->db->query("CALL sp_a_run ('SELECT','$increment_check_qry')");
$increment_check_result = $increment_check_info->result();
$increment_check_info->next_result();
$inc_employee_code = explode(",",$increment_check_result[0]->employee_code);
if(count($inc_employee_code) > 0){
if((int)$increment_type === 1){
$inc_columns_qry = 'SELECT DISTINCT(cw_arrear_cumulative.employee_code) as employee_code,cw_arrear_cumulative.effective_date,cw_arrear_cumulative.category,GROUP_CONCAT(DISTINCT(cw_arrear_cumulative.arrear_column) order by arrear_column ASC) as arrear_columns,GROUP_CONCAT(cw_arrear_cumulative.arrear_value order by arrear_column ASC) as arrear_values FROM `cw_arrear_cumulative` inner join cw_increment_approval on cw_increment_approval.prime_increment_approval_id = cw_arrear_cumulative.increment_approval_id where increment_apply_month = "'.$process_month.'" '.$where_inc.' group by employee_code';
}else
if((int)$increment_type === 2){
$inc_columns_qry = 'SELECT DISTINCT(employee_code) as employee_code,effective_date,category,GROUP_CONCAT(arrear_column order by arrear_column ASC) as arrear_columns,GROUP_CONCAT(arrear_value order by arrear_column ASC) as arrear_values FROM `cw_arrear_cumulative` where increment_apply_month = "'.$process_month.'" '.$where_inc.' group by employee_code';
}
$inc_columns_info = $this->db->query("CALL sp_a_run ('SELECT','$inc_columns_qry')");
$inc_columns_result = $inc_columns_info->result();
$inc_columns_info->next_result();
$increment_array = array();
$effective_array = array();
$middle_inc = array();
foreach($inc_columns_result as $key => $inc_rslt){
$category = $inc_rslt->category;
if($month_day_result){
$role = $month_day_result[$category]['category'];
$day_conditions = $month_day_result[$category]['day_conditions'];
$day_count = $month_day_result[$category]['day_count'];
$day_start = $month_day_result[$category]['day_start'];
$day_end = $month_day_result[$category]['day_end'];
if((int)$day_conditions === 3){
$sal_start = $day_start;
//For Current month between days increment
$date = new DateTime("01-$process_month 00:00:00");
$date->modify('-1 month');
$salary_start_date = $date->format("Y-m-$sal_start");
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}else{
$sal_start = '01';
$date = new DateTime("01-$process_month 00:00:00");
$salary_start_date = $date->format("Y-m-$sal_start");
if((int)$day_conditions === 2){
$salary_end_date = date("Y-m-t",strtotime($day_end."-".$process_month));
}else{
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}
}
}
$effective_date = $inc_rslt->effective_date;
$arrear_columns = explode(",",$inc_rslt->arrear_columns);
$arrear_values = explode(",",$inc_rslt->arrear_values);
$arrear_data = array_combine($arrear_columns,$arrear_values);
$effective_array[$inc_rslt->employee_code] = date("m-Y",strtotime($effective_date));
if(($effective_date < $salary_start_date) || ($effective_date > $salary_start_date && $effective_date < $salary_end_date)){
$increment_array[$inc_rslt->employee_code] = $arrear_data;
}
if($effective_date <= $salary_start_date){
$this->increment_update($inc_rslt->employee_code,$process_month,$inc_rslt->category);
}else{
$middle_inc[] = array('employee_code'=>$inc_rslt->employee_code,'category'=>$inc_rslt->category);
}
}
}
if((int)$process_income_tax_val === 1){
/*-------------------------------------------------------*/
/**-------------- Tax Process Query START --------------**/
/*-------------------------------------------------------*/
//get financial info
$fin_query = 'SELECT prime_financial_setting_id,start_date,end_date from cw_financial_setting where set_as_default_financial_year = 1 and trans_status = "1"';
$fin_data = $this->db->query("CALL sp_a_run ('SELECT','$fin_query')");
$fin_result = $fin_data->result();
$fin_data->next_result();
//Get Financial Data
$fin_set_id = $fin_result[0]->prime_financial_setting_id;
$start_date = $fin_result[0]->start_date;
$end_date = $fin_result[0]->end_date;
$process_date = date("Y-m-d",strtotime("01-".$process_month));
$actual_months = $this->get_months($start_date,$end_date);
$month_count_rslt = $this->month_count($process_date);
//Tax financial setting date details
$start_pre_text = date("d-m-Y",strtotime($start_date));
$end_pre_text = date("d-m-Y",strtotime($end_date));
$pre_tax_qry = 'select IFNULL((sum(monthly_tds)),0) as prev_tax,employee_code from `cw_transactions` where trans_status = 1 and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") >= date_format(str_to_date("'.$start_pre_text.'", "%m-%Y"), "%Y-%m") and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") <= date_format(str_to_date("'.$end_pre_text.'", "%m-%Y"), "%Y-%m")';
$pre_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$pre_tax_qry')");
$pre_tax_result = $pre_tax_data->result_array();
$pre_tax_data->next_result();
$pre_tax_data = array_reduce($pre_tax_result, function ($result, $arr) {
$result[$arr['employee_code']] = $arr['prev_tax'];
return $result;
}, array());
$pf_match_query = 'SELECT formula FROM `cw_section_matching` WHERE tax_sub_section = 11 and trans_status = 1';
$pf_match_data = $this->db->query("CALL sp_a_run ('SELECT','$pf_match_query')");
$pf_match_result = $pf_match_data->result();
$pf_match_data->next_result();
$pf_match_formula = $pf_match_result[0]->formula;
$formula_qry = 'SELECT payroll_formula FROM cw_payroll_formula where out_column = "pt_projection" and trans_status = 1 and fandf_only = 0 order by abs(formula_order),abs(formula_mode) asc';
$formula_data = $this->db->query("CALL sp_a_run ('SELECT','$formula_qry')");
$formula_result = $formula_data->result();
$formula_data->next_result();
$pt_projection_formula = $formula_result[0]->payroll_formula;
//Check Tax Calculation Exist Query
$tax_exist_qry = 'SELECT count(*) as count_val,prime_tax_calculation_id,emp_code from cw_tax_calculation where process_month = "'.$process_month.'" and trans_status = 1';
$tax_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$tax_exist_qry')");
$tax_exist_result = $tax_exist_data->result();
$tax_exist_data->next_result();
$tax_calculation_id = $tax_exist_result[0]->prime_tax_calculation_id;
$tax_exist_result = json_decode(json_encode($tax_exist_result), true);
$tax_data = array_reduce($tax_exist_result, function ($result, $arr) {
$result[$arr['emp_code']] = $arr['count_val'];
return $result;
}, array());
//Get Ptax Projection formula
$pt_projection_qry = 'select manual_entry,matching_components from cw_projection_settings where projection_components = "professional_tax" and trans_status = 1';
$pt_projection_data = $this->db->query("CALL sp_a_run ('SELECT','$pt_projection_qry')");
$pt_projection_result = $pt_projection_data->result();
$pt_projection_data->next_result();
$manual_entry = $pt_projection_result[0]->manual_entry;
$matching_components = $pt_projection_result[0]->matching_components;
$pt_projection_formula = "";
if((int)$manual_entry === 1){ //manual entry yes for no need system calculated for pt projections.
$pt_projection_formula = $matching_components;
}
//Get PF Projection formula
$pf_proj_qry = 'SELECT payroll_formula FROM cw_payroll_formula where out_column = "pf_projection" and trans_status = 1 and fandf_only = 0 order by abs(formula_order),abs(formula_mode) asc';
$pf_proj_data = $this->db->query("CALL sp_a_run ('SELECT','$pf_proj_qry')");
$pf_proj_result = $pf_proj_data->result();
$pf_proj_data->next_result();
$pf_proj_formula = $pf_proj_result[0]->payroll_formula;
//get earning components
$earnings_query = 'SELECT GROUP_CONCAT(earnings) as earnings,GROUP_CONCAT(formula) as formula,GROUP_CONCAT(view_name) as view_name,GROUP_CONCAT(gross_section) as gross_section FROM `cw_income_matching` INNER JOIN cw_form_setting on cw_form_setting.label_name = cw_income_matching.earnings WHERE cw_income_matching.trans_status = 1';
$earnings_data = $this->db->query("CALL sp_a_run ('SELECT','$earnings_query')");
$earnings_result = $earnings_data->result();
$earnings_data->next_result();
//Gross Income Details
$gross_other_income_qry = 'select GROUP_CONCAT(other_income_column) as other_income_column from cw_other_income inner join cw_income_type on cw_income_type.prime_income_type_id =cw_other_income.income_type where cw_other_income.trans_status = 1 and (cw_other_income.income_type = 3 or cw_other_income.income_type = 4 or cw_other_income.income_type = 5) order by cw_income_type.income_type';
$gross_other_income_info = $this->db->query("CALL sp_a_run ('SELECT','$gross_other_income_qry')");
$gross_other_income_result = $gross_other_income_info->result();
$gross_other_income_info->next_result();
$other_income_column = $gross_other_income_result[0]->other_income_column;
if($other_income_column){
$other_income_entry_query = 'SELECT '.$other_income_column.',emp_code FROM cw_other_income_entry WHERE cw_other_income_entry.trans_status = 1 and effective_month = "'.$process_month.'"';
$other_income_entry_data = $this->db->query("CALL sp_a_run ('SELECT','$other_income_entry_query')");
$other_income_entry_result = $other_income_entry_data->result_array();
$other_income_entry_data->next_result();
$other_income_array = array_reduce($other_income_entry_result, function ($result, $arr) {
$result[$arr['emp_code']] = $arr;
return $result;
}, array());
}
//House rent Allowance
$hra_query = 'SELECT formula,actual_rent_paid_percentage,metro_percentage,non_metro_percentage,education_cess_percentage FROM `cw_financial_setting` WHERE cw_financial_setting.trans_status = 1 and set_as_default_financial_year = 1';
$hra_data = $this->db->query("CALL sp_a_run ('SELECT','$hra_query')");
$hra_result = $hra_data->result();
$hra_data->next_result();
//Get Section Query except Section 10
$section_query = 'SELECT tax_section_column,prime_tax_sub_section_id,tax_subsection_column,tax_act_details,tax_subsection_limit,cw_tax_section.tax_section,bill_required,exemption_method,section_limit FROM `cw_tax_section` inner join cw_tax_sub_section on cw_tax_sub_section.tax_section = cw_tax_section.prime_tax_section_id WHERE cw_tax_sub_section.tax_section != 1 AND cw_tax_sub_section.trans_status = 1 order by prime_tax_section_id,subsection_order ASC';
$section_data = $this->db->query("CALL sp_a_run ('SELECT','$section_query')");
$section_result = $section_data->result();
$section_data->next_result();
$section_array = array();
$dec_sub_sec_columns = "tax_house_rent,childran_elig,";
$proof_sub_sec_columns = "tax_house_rent_proof,childran_elig_proof,";
foreach ($section_result as $key => $value){
$section_array[$value->tax_section][] = array("tax_section_column"=>$value->tax_section_column,"prime_tax_sub_section_id"=>$value->prime_tax_sub_section_id,"tax_subsection_column"=>$value->tax_subsection_column,"tax_act_details"=>$value->tax_act_details,"tax_subsection_limit"=>$value->tax_subsection_limit,"bill_required"=>$value->bill_required,"exemption_method"=>$value->exemption_method,"section_limit"=>$value->section_limit);
if($value->tax_subsection_column){
$dec_sub_sec_columns .= $value->tax_subsection_column.",";
$proof_sub_sec_columns .= $value->tax_subsection_column.",";
}
}
$dec_sub_sec_columns = rtrim($dec_sub_sec_columns,",");
$proof_sub_sec_columns = rtrim($proof_sub_sec_columns,",");
$section_dec_query = 'SELECT '.$dec_sub_sec_columns.',emp_code FROM `cw_declaration_entry` WHERE effective_month = "'.$process_month.'" and trans_status = 1';
$section_dec_data = $this->db->query("CALL sp_a_run ('SELECT','$section_dec_query')");
$section_dec_result = $section_dec_data->result_array();
$section_dec_data->next_result();
$dec_emp_data = array_reduce($section_dec_result, function ($result, $arr) {
$result[$arr['emp_code']] = $arr;
return $result;
}, array());
$proof_hra_query = 'select '.$proof_sub_sec_columns.',emp_code FROM `cw_tax_proof_entry` WHERE trans_status = 1';
$proof_hra_data = $this->db->query("CALL sp_a_run ('SELECT','$proof_hra_query')");
$proof_hra_result = $proof_hra_data->result();
$proof_hra_data->next_result();
$proof_emp_data = array_reduce($proof_hra_result, function ($result, $arr) {
$result[$arr['emp_code']] = $arr;
return $result;
}, array());
//Projection Data
$projection_query = 'SELECT GROUP_CONCAT(earnings) as proj_earnings,GROUP_CONCAT(view_name) as proj_view_name,GROUP_CONCAT(matching_components) as projection FROM `cw_income_matching` INNER JOIN cw_form_setting on cw_form_setting.label_name = cw_income_matching.matching_components
WHERE cw_income_matching.trans_status = 1 order by prime_income_matching_id asc';
$projection_data = $this->db->query("CALL sp_a_run ('SELECT','$projection_query')");
$projection_result = $projection_data->result();
$projection_data->next_result();
$earnings_columns = explode(",",$earnings_result[0]->earnings);
$view_array = explode(",",$earnings_result[0]->view_name);
$formula_array = explode(",",$earnings_result[0]->formula);
$gross_array = explode(",",$earnings_result[0]->gross_section);
$projection_columns = explode(",",$projection_result[0]->projection);
$proj_earnings = explode(",",$projection_result[0]->proj_earnings);
$proj_view_array = explode(",",$projection_result[0]->proj_view_name);
$proj_combine = array_combine($proj_earnings,$projection_columns);
$view_det = array_combine($earnings_columns,$view_array);
/** Get PT Projection value START **/
if($pt_projection_formula){
$pt_projection_formula = str_replace('@', '', $pt_projection_formula);
$pt_proj_query = 'SELECT '.$pt_projection_formula.' AS pt_proj,employee_code FROM cw_transactions WHERE cw_transactions.trans_status = 1 and employee_code = "'.$employee_code.'"';
$pt_proj_data = $this->db->query("CALL sp_a_run ('SELECT','$pt_proj_query')");
$pt_proj_result = $pt_proj_data->result();
$pt_proj_data->next_result();
//$pt_proj_amt = $pt_proj_result[0]->pt_proj;
$pt_proj_result = json_decode(json_encode($pt_proj_result), true);
$pt_proj_amt_data = array_reduce($pt_proj_result, function ($result, $arr) {
$result[$arr['employee_code']] = $arr['pt_proj'];
return $result;
}, array());
}
/** Get PT Projection value END **/
//Dynamic Mapping column
$pt_mapping_data = $this->Hr_methods_model->get_mapped_db_column(3);
$professional_tax_db = $pt_mapping_data['professional_tax_amount'];
//Get TAX Slab
$tax_slab_query = 'SELECT min_age,max_age,tax_range_start,tax_range_end,tax_amount,cw_slap_type.slap_type,cw_slap_type.prime_slap_type_id FROM `cw_income_tax_slab_income_tax_slab_details` inner join cw_slap_type on cw_slap_type.prime_slap_type_id = cw_income_tax_slab_income_tax_slab_details.prime_income_tax_slab_id inner join cw_income_tax_slab on cw_income_tax_slab.slap_type = cw_slap_type.prime_slap_type_id WHERE cw_income_tax_slab_income_tax_slab_details.trans_status = 1';
$tax_slab_data = $this->db->query("CALL sp_a_run ('SELECT','$tax_slab_query')");
$tax_slab_result = $tax_slab_data->result();
$tax_slab_data->next_result();
$sec10_query = 'SELECT tax_section_column,tax_subsection_column,tax_act_details,earnings,tax_subsection_limit,cw_tax_section.tax_section,bill_required,exemption_method FROM `cw_income_matching` inner join cw_tax_sub_section on cw_tax_sub_section.prime_tax_sub_section_id = cw_income_matching.excemption_component inner join cw_tax_section on cw_tax_section.prime_tax_section_id = cw_tax_sub_section.tax_section order by subsection_order';
$sec10_data = $this->db->query("CALL sp_a_run ('SELECT','$sec10_query')");
$sec10_result = $sec10_data->result();
$sec10_data->next_result();
/*-------------------------------------------------------*/
/**------------- Tax Process Query END -----------------**/
/*-------------------------------------------------------*/
}
//Get Increment mapping Columns
//$mapping_data = $this->Hr_methods_model->get_mapped_db_column(12);
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_info_query')");
$emp_rslt = $emp_data->result_array();
$emp_data->next_result();
$trans_array = array();
foreach($emp_rslt as $rslt){
$prime_employees_id = (int)$rslt['prime_employees_id'];
$employee_code = $rslt['employee_code'];
$role = (int)$rslt['role'];
$tax_location = (int)$rslt['professional_tax_location'];
$emp_age = (int)$rslt['emp_age'];
$trans_array[$employee_code] = $rslt;
if((int)$process_income_tax_val === 1){
$trans_array = $this->tax_process($trans_array,$employee_code,$process_month,$process_proof_wise_val,$tax_location,$role,$emp_age,$tax_data,$tax_calculation_id,$pf_match_formula,$pt_proj_amt_data,$pf_proj_formula,$earnings_result,$other_income_array,$hra_result,$section_array,$earnings_columns,$proj_combine,$gross_array,$formula_array,$fin_result,$professional_tax_db,$emp_proj_data,$tax_slab_result,$process_date,$actual_months,$month_count_rslt,$fin_set_id,$dec_emp_data,$proof_emp_data,$sec10_result,$pre_tax_data);
}
}
//Loan Data Array Merge
$trans_array = array_replace_recursive($trans_array, $loan_array);
//Increment Data Array Merge
$trans_array = array_replace_recursive($trans_array, $increment_array);
//Middle Increment Update
if(!empty($middle_inc)){
foreach($middle_inc as $middle){
$category = $middle['category'];
$emp_code = $middle['employee_code'];
$this->increment_update($emp_code,$process_month,$category);
}
}
//print_r($trans_array); die;
$payroll_rslt = $this->Payroll_calculation_model->Payroll_calculation($trans_array);
$table_view = $this->transaction_data($process_month,$process_role,$process_emp_code);
$mi_upd_query = 'UPDATE cw_monthly_input SET payroll_status = 1,trans_updated_by = "'.$this->logged_id.'", trans_updated_date = "'.date("Y-m-d H:i:s").'" where trans_status= 1 and input_status = 1 '.$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');
$date = "01-".$process_month;
$get_month_yrs = date('m-Y', strtotime($date. ' + 1 months'));
$last_dlt_qry = 'SELECT count(*) as dlt_count FROM cw_transactions WHERE trans_status = 1 and transactions_month = "' . $get_month_yrs . '"' . $where_query;
$last_dlt_info = $this->db->query("CALL sp_a_run ('SELECT','$last_dlt_qry')");
$last_dlt_rslt = $last_dlt_info->result();
$last_dlt_info->next_result();
$dlt_count = $last_dlt_rslt[0]->dlt_count;
if((int)$dlt_count > 0){
echo json_encode(array('success' => FALSE,'message' =>"Next Month Payroll Already Processed!!!"));
exit();
}
//Loan Update paid Status
$upd_query = 'UPDATE cw_loan_installment SET paid_status = 0 where install_year ="'.$process_month.'" 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 . '"' . $where_query;
$save_result = $this->db->query("CALL sp_a_run ('RUN','$process_payroll_query')");
if($save_result){
$process_payroll_delete_query = 'DELETE from cw_transactions WHERE trans_status = 1 and transactions_month = "' . $process_month . '"' . $where_query;
$this->db->query("CALL sp_a_run ('RUN','$process_payroll_delete_query')");
$process_payroll_upd_query = 'UPDATE cw_transactions_dlt SET trans_deleted_by = "' . $logged_id . '",trans_deleted_date = DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%S") WHERE trans_status = 1 and process_month = "' . $process_month . '"' . $where_query;
$this->db->query("CALL sp_a_run ('RUN','$process_payroll_upd_query')");
}
//update mi
$mi_upd_query = 'UPDATE cw_monthly_input SET payroll_status = 0,trans_updated_by = "'.$this->logged_id.'", trans_updated_date = "'.date("Y-m-d H:i:s").'" where trans_status = 1 '.$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')");
}
//Increment previous value update in master
$inc_columns_qry = 'SELECT employee_code as employee_code,column_name,current_value FROM `cw_increment` where apply_on = "'.$process_month.'" '.$where_inc.' GROUP BY employee_code,column_name';
$inc_columns_info = $this->db->query("CALL sp_a_run ('SELECT','$inc_columns_qry')");
$inc_columns_result = $inc_columns_info->result_array();
$inc_columns_info->next_result();
$increment_array = array_reduce($inc_columns_result, function ($result, $arr) {
$result[$arr['employee_code']][$arr['column_name']] = $arr['current_value'];
return $result;
}, array());
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 Process 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__)."/"."Payroll_calculation_model.php";
$filename = str_replace('controllers','models',$filename);
$earning_col = "";
$deduction_col = "";
$can_process = false;
if(file_exists($filename)){
$created_date = date("Y-m-d H:i:s",filemtime($filename));
$isupdated_qry = 'SELECT count(*) as tot_count FROM cw_payroll_formula WHERE trans_created_date >= "'.$created_date.'" or trans_updated_date >= "'.$created_date.'"';
$isupdated_data = $this->db->query("CALL sp_a_run ('SELECT','$isupdated_qry')");
$isupdated_result = $isupdated_data->result();
$isupdated_data->next_result();
$tot_count = (int)$isupdated_result[0]->tot_count;
$loan_updated_qry = 'SELECT count(*) as tot_count FROM cw_loan WHERE trans_created_date >= "'.$created_date.'" or trans_updated_date >= "'.$created_date.'"';
$loan_updated_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_updated_qry')");
$loan_updated_result = $loan_updated_data->result();
$loan_updated_data->next_result();
$loan_tot_count = (int)$loan_updated_result[0]->tot_count;
if((int)$tot_count > 0 || (int)$loan_tot_count > 0){
$can_process = true;
}else{
$can_process = false;
$earn_ded_updated_qry = 'SELECT count(*) as earn_ded_count FROM cw_form_setting WHERE prime_module_id = "employees" AND cw_form_setting.trans_status = 1 and (gross_check = 1 or deduction_check =1) and trans_created_date >= "'.$created_date.'" or trans_updated_date >= "'.$created_date.'"';
$earn_ded_updated_info = $this->db->query("CALL sp_a_run ('SELECT','$earn_ded_updated_qry')");
$earn_ded_updated_rslt = $earn_ded_updated_info->result();
$earn_ded_updated_info->next_result();
$earn_ded_count = (int)$earn_ded_updated_rslt[0]->earn_ded_count;
if((int)$earn_ded_count > 0){
$can_process = true;
}
}
}else{
$can_process = true;
}
//$can_process = true;
//get function name and map column and input value
$statutory_map_qry = 'select map_column,statutory_function_name as function_name,input_column from cw_payroll_function inner join cw_statutory_function on cw_statutory_function.prime_statutory_function_id =cw_payroll_function.function_name where cw_payroll_function.trans_status = 1';
$statutory_map_data = $this->db->query("CALL sp_a_run ('SELECT','$statutory_map_qry')");
$statutory_map_result = $statutory_map_data->result();
$statutory_map_data->next_result();
$statutory_map_list = array();
foreach($statutory_map_result as $statutory_map){
$map_column = $statutory_map->map_column;
$function_name = $statutory_map->function_name;
$input_column = $statutory_map->input_column;
$statutory_map_list[$map_column] = array('map_column'=>$map_column,'function_name'=> $function_name, 'input_column'=>$input_column, 'sts'=>false);
}
if($can_process){
$map_qry = 'select formula_for,out_column,payroll_formula,formula_mode from cw_payroll_formula where cw_payroll_formula.trans_status = 1';
$data = $this->db->query("CALL sp_a_run ('SELECT','$map_qry')");
$map_result = $data->result();
$data->next_result();
$map_list = array();
foreach($map_result as $map_result_map){
$formula_for = $map_result_map->formula_for;
$out_column = $map_result_map->out_column;
$payroll_formula = $map_result_map->payroll_formula;
$formula_mode = $map_result_map->formula_mode;
$this->sort_formula($formula_for,$out_column,$payroll_formula,$formula_mode);
}
//Total Earnings and Total Deduction Auto Calculate
$earn_ded_qry = 'SELECT CASE WHEN gross_check = 1 THEN GROUP_CONCAT("$trans[""",label_name,"""]+") END as total_earnings,CASE WHEN deduction_check = 1 THEN GROUP_CONCAT("$trans[""",label_name,"""]+") END as total_deductions,input_for FROM cw_form_setting WHERE prime_module_id = "employees" AND cw_form_setting.trans_status = 1 and (gross_check = 1 or deduction_check =1) group by input_for order by input_for';
$earn_ded_info = $this->db->query("CALL sp_a_run ('SELECT','$earn_ded_qry')");
$earn_ded_rslt = $earn_ded_info->result_array();
//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 and fandf_only = 0 order by abs(formula_order),abs(formula_mode) asc';
$formula_data = $this->db->query("CALL sp_a_run ('SELECT','$formula_qry')");
$formula_result = $formula_data->result();
$formula_data->next_result();
$formula_list = array();
foreach($formula_result as $formula){
$formula_for = $formula->formula_for;
$formula_type = $formula->formula_type;
$out_column = $formula->out_column;
$payroll_formula = $formula->payroll_formula;
$formula_order = $formula->formula_order;
$formula_mode = $formula->formula_mode;
$round_value = $formula->round_value;
$formula_list[$formula_for][$out_column] = array("payroll_formula"=>$payroll_formula,"formula_mode"=>$formula_mode,"round_value"=>$round_value);
}
$formula_code = "\n\t\t\t".' $employee_code = $trans["employee_code"]; $trans["transactions_month"] = $trans["process_month"];';
$count = 0;
$role_array = array();
foreach($formula_list as $role => $formula){
$role_array[] = $role;
$count++;
$sub_formula = "";
$query_key = "";
$query_value = "";
foreach($formula as $key=>$value){
$payroll_formula = $value['payroll_formula'];
$formula_mode = (int)$value['formula_mode'];
$round_value = $value['round_value'];
$preg_match = preg_match_all('#\@(.*?)\@#', $payroll_formula, $match);
foreach($match[1] as $for_rslt){
$find_value = "@$for_rslt@";
$for_value = '$trans["'.$for_rslt.'"]';
$payroll_formula = str_replace($find_value,$for_value,$payroll_formula);
}
//$payroll_formula = str_replace("dz(","\$this->dz(",$payroll_formula);
if(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('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["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 Payroll_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\tpublic function $fname
\$month_day_qry = 'SELECT category,day_conditions,day_count,day_start,day_end from cw_month_day where cw_month_day.trans_status = 1 ';
\$month_day_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$month_day_qry')\");
\$month_day_result = \$month_day_data->result_array();
\$month_day_data->next_result();
\$this->month_day_res = array_reduce(\$month_day_result, function (\$result, \$arr) {
\$result[\$arr['category']] = \$arr;
return \$result;
}, array()); \n\t
//get tax settings info
\$ptax_qry = 'select cw_professional_tax.prime_professional_tax_id,location,calculation_period,ptax_deduction_month_first as first_period,ptax_deduction_month_second as second_period, osm_first_end,osm_second_end,osm_first_start,osm_second_start from cw_professional_tax where cw_professional_tax.trans_status = 1';
\$ptax_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$ptax_qry')\");
\$ptax_result = \$ptax_data->result_array();
\$ptax_data->next_result();
\$this->ptax_rslt = array_reduce(\$ptax_result, function (\$result, \$arr) {
\$result[\$arr['location']] = \$arr;
return \$result;
}, array()); \n\t
// get_document_fees
\$payroll_exist_query = 'SELECT employee_code,count(employee_code) as pay_count from cw_transactions where termination_status = 0 and trans_status = 1 group by employee_code';
\$payroll_exist_info = \$this->db->query(\"CALL sp_a_run ('RUN','\$payroll_exist_query')\");
\$payroll_exist_result = \$payroll_exist_info->result_array();
\$payroll_exist_info->next_result();
\$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,esi_location,esi_eligibility from cw_employees where trans_status = 1';
\$emp_date_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$emp_date_qry')\");
\$emp_date_rslt = \$emp_date_data->result_array();
\$emp_date_data->next_result();
\$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 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_data, 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 DRA Fee
public function get_dra_amount(\$branch,\$date_of_joining,\$dra_prev,\$dra_completion_status,\$employee_code){
if((int)\$dra_completion_status === 2){
// Get Branch wise DRA Amount
\$dra_amount_qry = 'SELECT dra_amount,actual_amount from cw_dra_amount where cw_dra_amount.trans_status = 1 and cw_dra_amount.branch = \"'.\$branch.'\" and start_date <= \"'.\$date_of_joining.'\" and end_date >= \"'.\$date_of_joining.'\"';
\$dra_amount_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$dra_amount_qry')\");
\$dra_amount_result = \$dra_amount_data->result();
\$dra_amount_data->next_result();
\$dra_amount = \$dra_amount_result[0]->dra_amount;
\$actual_amount = \$dra_amount/3;
\$payroll_exist = (int)\$this->payroll_count[\$employee_code];
\$payroll_exist_count = \$payroll_exist + 1;
if(\$dra_prev >= \$dra_amount){
return 0;
}else{
\$payroll_check_list_1 = array(1,2,3);
\$payroll_check_list_2 = array(2,3,4);
if(in_array(\$employee_code, \$this->sup_emp)){
if(in_array(\$payroll_exist_count, \$payroll_check_list_1)){
return \$actual_amount;
}else{
return 0;
}
}else{
if(in_array(\$payroll_exist_count, \$payroll_check_list_2)){
return \$actual_amount;
}else{
return 0;
}
}
}
}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;
}
//Get ESI STATUS 20SEP2022 BSK
public function get_esi_status(\$earned_gross,\$employee_code,\$role,\$process_month){
\$statutory_rslt = \$this->statutory_arr[\$role];
\$esi_limit = \$statutory_rslt['esi_limit'];
\$esi_conditions = \$statutory_rslt['esi_conditions'];
\$esi_start_check_month = \$statutory_rslt['esi_start_check_month'];
\$esi_end_check_month = \$statutory_rslt['esi_end_check_month'];
\$process_month = \$statutory_rslt['process_month'];
\$process_month_val = \$statutory_rslt['process_month_val'];
\$esi_loc_db = \$this->get_map_arr['esi_loc']['db_column'];
\$esi_elig_db = \$this->get_map_arr['esi_elig']['db_column'];
//location nil is default
//1 -- Nil default no esi sts 2
/*\$select_loc_qry = 'select '.\$esi_loc_db.' as esi_loc from cw_employees where trans_status = 1 and role = \"'.\$role.'\" and employee_code = \"'.\$employee_code.'\"';
\$select_loc_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$select_loc_qry')\");
\$select_loc_result = \$select_loc_data->result_array();
\$select_loc_data->next_result();*/
\$esi_location = \$this->emp_date_arr[\$employee_code][\$esi_loc_db];
\$esi_eligibility = \$this->emp_date_arr[\$employee_code][\$esi_elig_db];
if((int)\$esi_location){
\$upd_query = 'UPDATE cw_employees SET esi_eligibility = 2 WHERE trans_status = 1 and role =\"'.\$role.'\" and employee_code=\"'.\$employee_code.'\"';
\$update_info = \$this->db->query(\"CALL sp_a_run ('UPDATE','\$upd_query')\");
\$esi_sts = 2;
return \$esi_sts;
}else{
//esi eligibility 1= yes 2 = no;
if((\$esi_start_check_month === \$process_month_val) || (\$esi_end_check_month === \$process_month_val)){
if((int)\$earned_gross > (int)\$esi_limit){
\$upd_query = 'UPDATE cw_employees SET esi_eligibility = 2 WHERE trans_status = 1 and role =\"'.\$role.'\" and employee_code=\"'.\$employee_code.'\"';
\$update_info = \$this->db->query(\"CALL sp_a_run ('UPDATE','\$upd_query')\");
\$esi_sts = 2;
return \$esi_sts;
}else{
\$esi_sts = 1; //yes esi
return \$esi_sts;
}
}else{
\$select_esi_elig_qry = 'select '.\$esi_loc_db.' as esi_elig from cw_employees where trans_status = 1 and role = \"'.\$role.'\" and employee_code = \"'.\$employee_code.'\"';
\$select_esi_elig_data = \$this->db->query(\"CALL sp_a_run ('SELECT','\$select_esi_elig_qry')\");
\$select_esi_elig_result = \$select_esi_elig_data->result();
\$select_esi_elig_data->next_result();
\$esi_sts = \$select_esi_elig_result[0]->esi_elig;
return \$esi_sts; //actual esi status from DB//
}
}
}
//GET LWF EMPLOYER (COMPANY AMOUNT) FUNCTION
public function get_lwf_employer(\$lwf_location,\$process_month){
\$process_month = explode(\"-\",\$process_month);
\$process_month_val = \$process_month[0];
//\$process_month_val = 12;
\$select_lwf_result = \$this->lwf_emp_arr[\$lwf_location];
\$pay_month = \$select_lwf_result['pay_month'];
\$company_amount = \$select_lwf_result['company_amount'];
\$lwf_comp_amt = 0;
if(\$pay_month == \"process_month_val\"){
\$lwf_comp_amt = \$company_amount;
}
return \$lwf_comp_amt;
}
//GET LWF EMPLOYEE (EMPLOYEE AMOUNT) FUNCTION
public function get_lwf_employee(\$lwf_location,\$process_month){
\$process_month = explode(\"-\",\$process_month);
\$process_month_val = \$process_month[0];
\$select_lwf_result = \$this->lwf_emp_arr[\$lwf_location];
\$pay_month = \$select_lwf_result['pay_month'];
\$employee_amount = \$select_lwf_result['employee_amount'];
\$lwf_emp_amt = 0;
if(\$pay_month == \"process_month_val\"){
\$lwf_emp_amt = \$employee_amount;
}
return \$lwf_emp_amt;
}
//formula PD = MD-LD-DD-S_DD;
//find re-leaving date for resigning employee details check this values 13/12/2021
public function get_seperation_day(\$employee_code,\$role,\$process_month){
\$sep_day = \$this->emp_date_arr[\$employee_code]['last_working_date'];
if(\$sep_day && \$sep_day !== '0000-00-00' && \$sep_day !== '1970-01-01'){
\$month_day_result = \$this->month_day_res[\$role];
\$day_conditions = \$month_day_result['day_conditions'];
\$day_count = \$month_day_result['day_count'];
\$day_start = \$month_day_result['day_start'];
\$day_end = \$month_day_result['day_end'];
if((int)\$day_conditions === 1){
//STATIC DAYS
\$process_date = \$day_count.'-'.\$process_month;
\$process_date = date('d-m-Y', strtotime(\$process_date));
\$process_date = strtotime(\$process_date);
\$sep_day = strtotime(\$sep_day);
\$diff = \$process_date - \$sep_day;
\$separation_day = round(\$diff / 86400);
}else
if((int)\$day_conditions === 2){
//CALENDER DAYS
\$trans_month = explode(\"-\",\$process_month);
\$month_val = \$trans_month[0];
\$year_val = \$trans_month[1];
\$day_count = cal_days_in_month(CAL_GREGORIAN, \$month_val, \$year_val);
\$process_date = \$day_count.'-'.\$process_month;
\$process_date = date('d-m-Y', strtotime(\$process_date));
\$process_date = strtotime(\$process_date);
\$sep_day = strtotime(\$sep_day);
\$diff = \$process_date - \$sep_day;
\$separation_day = round(\$diff / 86400);
}else
if((int)\$day_conditions === 3){
//CUTOFF DAYS
\$trans_month = explode(\"-\",\$process_month);
\$month_val = \$trans_month[0];
\$year_val = \$trans_month[1];
\$process_date = \$day_end.'-'.\$month_val.'-'.\$year_val;
\$process_date = strtotime(\$process_date);
\$sep_day = strtotime(\$sep_day);
\$diff = \$process_date - \$sep_day;
\$separation_day = round(\$diff / 86400);
}
}else{
\$separation_day = 0;
}
if(\$separation_day < 0){
\$separation_day = 0;
}
return \$separation_day;
}
public function get_gratuity(\$employee_code,\$role){
\$check_eligibilty_result = \$this->gratuity_arr[\$role];
\$working_days = \$select_lwf_result['working_days'];
\$number_of_years = \$select_lwf_result['number_of_years'];
\$pay_days = \$select_lwf_result['pay_days'];
\$year_rounding = \$select_lwf_result['year_rounding'];
\$gratuity_formula = \$select_lwf_result['formula_detail'];
\$formula_rounding = \$select_lwf_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 = ((\$last_day-\$doj)/60/60/24)/365;
\$year_val = round(\$tot_year , 2);
//year rounding base working year is rounded
if((int)\$year_rounding === 1){
\$tot_year = \$year_val;
}else
if((int)\$year_rounding === 2){
\$tot_year = round(\$year_val);
}else
if((int)\$year_rounding === 3){
\$tot_year = ceil(\$year_val);
}else
if((int)\$year_rounding === 4){
\$tot_year = floor(\$year_val);
}
//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_array();
\$grat_formula_amt_data->next_result();
\$grat_formula_amt = \$grat_formula_amt_result[0]->gratuity;
\$gratuity_value = ((\$grat_formula_amt * \$pay_days)/\$working_days) * \$tot_year;
\$gratutity_amt = \$this->rounding_value(\$gratuity_value,\$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;
if(\$prof_tax < 0){
\$prof_tax = 0;
}
}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_amt = \$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 round(\$prof_tax);
}
\n\n}
\n?>";
$oldmask = umask(0);
$formula_temp_file = dirname(__FILE__)."/"."Payroll_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 = "";
if($process_role){
$search_val = ' and cw_transactions.'.$this->payroll_process_type.' in ('.$process_role.') ';
}else
if($process_emp_code){
$search_val = ' and cw_transactions.employee_code = "'.$process_emp_code.'" ';
}
$select_query = "";
$pick_query = "";
$final_qry = "";
$thead_line = "";
$thead = "";
$form_qry = 'SELECT prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "employees" and transaction_type in (1,2,3) and (earn_payroll_check = "1" or ded_payroll_check = "1") and trans_status = "1" order by payroll_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
$loan_column_qry = 'SELECT GROUP_CONCAT(label_name) as loan_columns FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND input_view_type IN (1,2) and loan_check = 1 ORDER BY prime_form_id';
// echo "$loan_column_qry";die;
$loan_column_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_column_qry')");
$loan_column_rslt = $loan_column_data->result();
$loan_column_data->next_result();
$loan_columns = array();
if($loan_column_rslt[0]->loan_columns){
$loan_columns = explode(",",$loan_column_rslt[0]->loan_columns);
}
$query_key = "";
$loan_array = array();
foreach ($loan_columns as $key => $loan_column){
//Generate Query Key For Loan
$query_key .= ",cw_transactions.".$loan_column."_total";
$query_key .= ",cw_transactions.".$loan_column."_installments";
$query_key .= ",cw_transactions.".$loan_column."_instal_count";
$query_key .= ",cw_transactions.".$loan_column."_balance";
$loan_array[] = $loan_column."_total";
$loan_array[] = $loan_column."_installments";
$loan_array[] = $loan_column."_instal_count";
$loan_array[] = $loan_column."_balance";
}
$loan_thead = "";
foreach($loan_array as $loan){
$loan = str_replace("_", " ", $loan);
$loan = ucwords($loan);
$loan_thead .= "<th>$loan</th>";
}
$table_name = "cw_transactions";
$i = 1;
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
if($label_name == "role"){
$view_name = "Category";
}
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else{
$select_query .= "$table_name.$label_name , ";
}
if((int)$i === 1){
$th_style = "class='hard_left'";
}else
if((int)$i === 2){
$th_style = "class='next_left'";
}else{
$th_style = "";
}
$thead_line .= "<th $th_style>$view_name</th>";
$i++;
}
$thead = "<tr>$thead_line $loan_thead</tr>";
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
// echo "$select_query";die;
$trans_status = " left join cw_employees on cw_employees.employee_code =cw_transactions.employee_code where cw_transactions.stop_pay_status !=1 and ".$table_name.".trans_status = 1 and transactions_month=\"".$process_month."\"";
$final_qry = "select $select_query $query_key from $table_name $pick_query $trans_status $search_val";
//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));
}
//excel maaping format
public function excel($module_id,$excel_format){
$excel_format_qry = 'select view_name,excel_line_column_name,excel_line_value from cw_util_excel_format_line inner join cw_form_setting on cw_form_setting.label_name = excel_line_column_name where excel_line_module_id = "'.$module_id.'" and prime_excel_format_id ="'.$excel_format.'" and cw_util_excel_format_line.trans_status = 1 GROUP BY cw_form_setting.label_name';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
//require_once APPPATH."/third_party/PHPExcel.php";
require_once APPPATH."/controllers/php_excel/PHPExcel.php";
$obj = new PHPExcel();
//Set the first row as the header row
foreach($excel_result as $excel){
$excel_line_column_name = $excel->view_name;
$excel_line_value = $excel->excel_line_value;
$obj->getActiveSheet()->setCellValue($excel_line_value."1", $excel_line_column_name);
}
// Rename worksheet name
$filename= $module_id.".xls"; //save our workbook as this file name
ob_end_clean();
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
ob_end_clean();
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = PHPExcel_IOFactory::createWriter($obj, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
echo json_encode(array('success' => TRUE, 'output' => $excelOutput));
}
//SAVE IMPORT FILE PATH
public function save_import(){
$module_id = $this->control_name;
$process_month = $this->input->post('transaction_month');
$excel_format = $this->input->post('excel_format');
$excel_file_path = $this->input->post('excel_file_path');
$excel_sheet_name = $this->input->post('excel_sheet_name');
$excel_start_row = $this->input->post('excel_start_row');
$excel_end_row = $this->input->post('excel_end_row');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$import_query = 'insert into cw_month_import (module_id,excel_format,excel_file_path,excel_sheet_name,excel_start_row,excel_end_row,process_month,trans_created_by,trans_created_date) value ("'.$module_id.'","'.$excel_format.'","'.$excel_file_path.'","'.$excel_sheet_name.'","'.$excel_start_row.'","'.$excel_end_row.'","'.$process_month.'","'.$logged_id.'","'.$today_date.'")';
$import_info = $this->db->query("CALL sp_a_run ('INSERT','$import_query')");
$import_result = $import_info->result();
$import_info->next_result();
$import_id = $import_result[0]->ins_id;
echo $this->do_excel_payroll_import($import_id);
}
//IMPORT DATA FROM FILE PATH
public function do_excel_payroll_import($import_id){
$filename = dirname(__FILE__)."/php_excel/PHPExcel/IOFactory.php";
include($filename);
if($import_id < 0){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}
$excel_path_qry = 'select * from cw_month_import where import_id = "'.$import_id.'"';
$excel_path_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_path_qry')");
$excel_path_result = $excel_path_info->result();
$excel_path_info->next_result();
if(!$excel_path_result){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}else{
$this->prime_table = "cw_transactions";
$excel_file_path = $excel_path_result[0]->excel_file_path;
$module_id = "transactions";
$excel_format = $excel_path_result[0]->excel_format;
$excel_sheet_name = (int)$excel_path_result[0]->excel_sheet_name;
$excel_row_start = (int)$excel_path_result[0]->excel_start_row;
$excel_row_end = (int)$excel_path_result[0]->excel_end_row;
$process_month = $excel_path_result[0]->process_month;
$lock_pay_qry = 'select * from cw_payroll where pay_month = "'.$process_month.'" and status = 1 and trans_status = 1';
$lock_pay_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_pay_qry')");
$lock_num_rows = $lock_pay_data->num_rows();
$lock_pay_data->next_result();
if((int)$lock_num_rows > 0){
return json_encode(array('success' => false, 'message' => "payroll is locked for this month, please unlocked to upload the files"));
exit(0);
}
$payroll_exist_query = 'select count(*) as pay_count from cw_transactions where transactions_month="'.$process_month.'"';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('RUN','$payroll_exist_query')");
$payroll_exist_result = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$payroll_exist_count = $payroll_exist_result[0]->pay_count;
if((int)$payroll_exist_count > 0){
return json_encode(array('success' => false, 'message' => "Already Payroll is proceed this month!!!"));
exit(0);
}
$format_qry = 'select * from cw_util_excel_format where prime_excel_format_id = "'.$excel_format.'" and cw_util_excel_format.trans_status = 1';
$format_info = $this->db->query("CALL sp_a_run ('SELECT','$format_qry')");
$format_rslt = $format_info->result();
$format_info->next_result();
if(!$format_rslt){
return json_encode(array('success' => false, 'message' => "Please add excel format before import"));
}else{
$exist_column_name = explode(",",$format_rslt[0]->exist_column_name);
$excel_format_qry = 'select unique_field,pick_list_import,view_name,label_name,field_type,pick_table,pick_list_type,pick_list,mandatory_field,field_isdefault,excel_line_column_name,excel_line_value from cw_util_excel_format_line inner join cw_form_setting on label_name = excel_line_column_name where excel_line_module_id = "'.$module_id.'" and prime_excel_format_id = "'.$excel_format.'" and cw_form_setting.prime_module_id = "employees" and cw_util_excel_format_line.trans_status = 1 order by prime_excel_format_line_id ASC';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_format_result = $excel_format->result();
$excel_format->next_result();
if(!$excel_format_result){
return json_encode(array('success' => false, 'message' => "Please map excel cell column before import"));
}else{
try{
$excel_obj = 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();
//Unique field check
$err_column_tabview = array();
$array_uniq = array();
foreach ($excel_format_result as $key => $value) {
$label_name = $value->label_name;
$view_name = $value->view_name;
$unique_field = $value->unique_field;
$field_type = $value->field_type;
$pick_table = $value->pick_table;
$pick_list_type = (int)$value->pick_list_type;
$excel_line_value = $value->excel_line_value;
$pick_list_import = $value->pick_list_import;
$pick_list = $value->pick_list;
$multi_get_cell_value = $sheet->rangeToArray("$excel_line_value$excel_row_start:$excel_line_value$total_rows", NULL, TRUE, TRUE, TRUE);
$common_multi_cell_value = $sheet->rangeToArray("$excel_line_value$excel_row_start:$excel_line_value$total_rows", NULL, TRUE, TRUE, TRUE);
$i = $excel_row_start;
foreach($common_multi_cell_value as $common_value){
foreach($common_value as $col_key =>$col_value){
if(empty($col_value) && !is_numeric($col_value)){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "columns are empty and invalid data is present please check it?";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}elseif((int)$field_type === 5 || (int)$field_type === 7){
if($pick_list_import !== 1){
if($pick_list_type === 1){
if($excel_line_column_name === "employee_code" || $excel_line_column_name === "user_id"){
$emp_code_qry = 'select count(*) as rslt_count from cw_employees where trans_status = 1 and employee_code = "'.$col_value.'"';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_code_qry')");
$emp_data_result = $emp_data->result();
$emp_data->next_result();
$rslt_count = $emp_data_result[0]->rslt_count;
if((int)$rslt_count === 0){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "is not exit in employee master please check it?";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}else
if($pick_table === "cw_category"){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query = 'select '.$pick_list.' from '.$pick_table.' where '.$pick_list_val_2.' = "'.$col_value.'"';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$pick_count = $pick_data->num_rows();
$pick_val_1 = $pick_result[0]->$pick_list_val_1;
if((int)$pick_count === 0){
$sts = 0;
$invalid_cat[] = $col_value;
}else{
$col_value = $pick_val_1;
}
$cat_inv = array_unique($invalid_cat);
$inv_cat = implode(",",$cat_inv);
$cat_sts = 1;
if(empty($inv_cat)){
$cat_sts = 0;
}
if($cat_sts){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "Invalid category name like $inv_cat, please check category and employee code!!!";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}else{
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query = 'select '.$pick_list.' from '.$pick_table.' where '.$pick_list_val_2.' = "'.$col_value.'"';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_count = $pick_data->num_rows();
$pick_data->next_result();
if((int)$pick_count === 0){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "column invalid data is present please check it?";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}
}
}
}elseif($field_type === 4){
$excel_cell_formate = $excel_obj->getActiveSheet()->getCell($col_key.$i)->getStyle()->getNumberFormat()->getFormatCode();
$cell_formate = str_replace("[$-14009]","",$excel_cell_formate);
$cell_formate = trim(strtoupper(str_replace(";@","",$cell_formate)));
$cell_formate = str_replace('\-', '-', $cell_formate);
if($cell_formate === "DD/MM/YYYY" || $cell_formate === "DD-MM-YYYY"){
if($cell_formate === "DD/MM/YYYY"){
$year_month_rslt = explode('/', $col_value);
}else
if($cell_formate === "DD-MM-YYYY"){
$year_month_rslt = explode('-', $col_value);
}
$date = $year_month_rslt[0];
$month = $year_month_rslt[1];
$year = $year_month_rslt[2];
$tot_days = cal_days_in_month(CAL_GREGORIAN,$month,$year);
if(((int)$date === 0) || ((int)$month === 0) || ((int)$year === 0)){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "Please enter valid date... Please map The Date Format Like (DD/MM/YYYY)";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}else
if((int)$month > 12){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "Invalid Month... Please map The Date Format Like (DD/MM/YYYY)";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}else
if((int)$date > (int)$tot_days){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "Invalid date... Please map The Date Format Like (DD/MM/YYYY)";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}else{
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "Invalid Date Format... Please map The Date Format Like (DD/MM/YYYY)";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}
$i++;
}
}
if((int)$unique_field === 1){
$get_duplicat_value = $sheet->rangeToArray("$excel_line_value$excel_row_start:$excel_line_value$total_rows", NULL, TRUE, TRUE, TRUE);
$get_duplicat_value = array_map('array_filter', $get_duplicat_value);//empty remove
$get_duplicat_value = array_filter($get_duplicat_value);
foreach ($get_duplicat_value as $current_key => $current_array) {
$get_excel_val = $current_array[$excel_line_value];
if($label_name === 'employee_code'){
$exist_query = 'select count(*) uniq_exist_count from '.$this->prime_table.' where '.$label_name.' = "'.$get_excel_val.'" and trans_status =1 and transactions_month="'.$process_month.'"';
}else{
$exist_query = 'select count(*) uniq_exist_count from '.$this->prime_table.' where '.$label_name.' = "'.$get_excel_val.'" and trans_status =1';
}
$exist_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
foreach ($get_duplicat_value as $search_key => $search_array) {
if($search_array["$excel_line_value"] == $current_array["$excel_line_value"]){
if ($search_key != $current_key) {
$err_column_array['error']["$excel_line_value$current_key"] = $view_name;
$msg_line = "duplicate data present in column, please check it?";
$err_column_tabview['error']["$excel_line_value$current_key"] = $view_name." ".$msg_line;
}
}
$exist_count = $exist_result[0]->uniq_exist_count;
$array_uniq[$label_name]['view_name'] = $view_name;
$array_uniq[$label_name]['label_name'] = $label_name;
}
if((int)$exist_count > 0){
$err_column_array['error']["$excel_line_value$current_key"] = $view_name;
$msg_line = "Data already exists for this Column";
$err_column_tabview['error']["$excel_line_value$current_key"] = $view_name." ".$msg_line;
}
}
}
}
// get module unique column
$uniq_field_qry = 'select view_name,label_name from cw_form_setting where cw_form_setting.prime_module_id = "'.$module_id.'" and input_view_type IN(1,2) and field_show = 1 and unique_field = 1 and trans_status = 1';
$uniq_field_info = $this->db->query("CALL sp_a_run ('SELECT','$uniq_field_qry')");
$uniq_field_rslt = $uniq_field_info->result_array();
$uniq_field_info->next_result();
$uniq_field_rslt = array_reduce($uniq_field_rslt, function($result, $arr){
$result[$arr['label_name']] = $arr;
return $result;
}, array());
$uniq_result = array_diff_key($uniq_field_rslt,$array_uniq);
if((int)$import_type === 1){
foreach ($uniq_result as $uniq_key => $uniq_val) {
$label_name = $uniq_val['label_name'];
$err_column_array['error']["label_name"] = $uniq_val['view_name'];
$msg_line = "Unique Field Column Missing";
$err_column_tabview['error'][$uniq_val['view_name']] = $uniq_val['view_name']." ".$msg_line;
}
}
$err_column_count = count($err_column_array['error']);
$err_column = implode(",",(array_unique($err_column_array['error'])));
if((int)$err_column_count > 0){
$table_info = $this->get_excel_error_ui($err_column_tabview);
echo json_encode(array('success'=>false,'message'=>"Column Wise Error",'table_info'=>$table_info));
exit();
}
for($row =$excel_row_start; $row <= $total_rows; $row++){
$prime_column_val = "";
$prime_cell_val = "";
$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]->date_of_joining;
$esi_elig = $emp_result[0]->esi_eligibility;
$pf_elig = $emp_result[0]->pf_eligibility;
$lwf_location = $emp_result[0]->lwf_location;
$tax_location = $emp_result[0]->professional_tax_location;
if(!$employees_id){
return json_encode(array('success' => false, 'message' => "Employee Code Not Exist.. Please Map the Employee Code Correctly"));
}else{
//only role and employee code is checking
if($prime_column_val){
$prime_id = "prime_".$module_id."_id";
$exist_val = "and ".rtrim($exist_val," and ");
$exist_query = "select count(*) as exist_count from cw_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,date_of_joining,esi_eligibility,pf_eligibility,lwf_location,professional_tax_location,transactions_month,process_month,trans_created_by,trans_created_date";
$prime_cell_val .= '"'.$employees_id.'","'.$emp_name.'","'.$doj.'","'.$esi_elig.'","'.$pf_elig.'","'.$lwf_location.'","'.$tax_location.'","'.$process_month.'","'.$process_month.'","'.$this->logged_id.'",'.'"'.$created_on.'"';
$prime_column_val = rtrim($prime_column_val,",");
$prime_cell_val = rtrim($prime_cell_val,",");
$prime_query = "insert into $this->prime_table ($prime_column_val) VALUES ($prime_cell_val)";
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$formula_process[] = $insert_id;
$status_info['Status'] = "Inserted to DB";
}
$status_array[] = $status_info;
}
}
}
}
$cat_inv = array_unique($invalid_cat);
$inv_cat = implode(",",$cat_inv);
$cat_sts = 1;
if(empty($inv_cat)){
$cat_sts = 0;
}
if($cat_sts){
echo json_encode(array('success'=>false,'message'=>"File not inserted in database",'cat_sts'=>$cat_sts, 'warning'=>"Invalid category name like $inv_cat, please check category and employee code!!!"));
}else{
echo json_encode(array('success'=>true,'message'=>"Successfully file imported"));
}
}
}
}
}
//ERROR COLUMN AND ROW DISPLAY FOR INVALID DATAS
public function get_excel_error_ui($err_column_tabview){
$table_info = "";
$th_line = "";
$tr_line = "";
foreach($err_column_tabview as $err_column){
foreach($err_column as $key => $value){
$tr_line .= "<tr><td>$key</td><td style='color:#ff0303 !important;'>$value</td></tr>";
}
}
$table_info = "<table class='table table-bordered'>
<thead>
<tr>
<th>Row and Column</th>
<th>Input Column</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $table_info;
}
//Increment Update
public function increment_update($emp_code,$process_month,$role){
$increment_column_qry = 'select * from cw_increment where employee_code="'.$emp_code.'" and apply_on="'.$process_month.'"';
$increment_column_info = $this->db->query("CALL sp_a_run ('SELECT','$increment_column_qry')");
$increment_column_result = $increment_column_info->result();
$increment_column_info->next_result();
//check formula
$formula_exist_qry = 'SELECT count(*) as formula_count FROM `cw_form_bind_input` INNER JOIN `cw_form_condition_formula` ON cw_form_condition_formula.prime_cond_id = cw_form_bind_input.input_cond_id WHERE input_cond_module_id ="employees" AND cond_order != 0 AND line_input_bind_col !="" AND cw_form_condition_formula.trans_status=1';
$formula_exist_data = $this->db->query("CALL sp_a_run ('RUN','$formula_exist_qry')");
$formula_exist_result = $formula_exist_data->result();
$formula_exist_data->next_result();
$formula_exist = $formula_exist_result[0]->formula_count;
$logged_id = $this->session->userdata('logged_id');
$update_date = date("Y-m-d H:i:s");
$update_qry = "";
$trans_array = array();
$column_array = array();
$new_array = array();
foreach($increment_column_result as $incr_rslt){
$emp_code = $incr_rslt->employee_code;
$column_name = $incr_rslt->column_name;
$new_value = $incr_rslt->new_value;
$diff_value = $incr_rslt->difference_value;
if((int)$formula_exist > 0){
$column_array[] = $column_name;
$new_array[] = $new_value;
}else{
if((int)$diff_value !== 0){
$update_qry .= $column_name.'="'.$new_value.'",';
}
}
}
$trans_array[$emp_code] = array_combine($column_array,$new_array);
if((int)$formula_exist > 0){
$trans_array[$emp_code]['role'] = $role;
$new_array = $this->Increment_calculation_model->increment_calculation($trans_array,$emp_code);
foreach($new_array[$emp_code] as $key => $value){
if($key !== "role"){
$update_qry .= $key.'="'.$value.'",';
}
}
}
$update_qry .= 'trans_updated_by = "'.$logged_id.'", trans_updated_date = "'.$update_date.'"';
$upd_inc_query = 'UPDATE cw_employees SET '.$update_qry.' WHERE employee_code = "'.$emp_code.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_inc_query')");
return true;
}
//get date difference_value
public function dateDiff($date1, $date2){
$date1_ts = strtotime($date1);
$date2_ts = strtotime($date2);
$diff = $date2_ts - $date1_ts;
return round($diff / 86400);
}
// NEHA EDIT 07-DEC-2019
//PAYROLL PROCESS GET ACTIVITY REMINDER INFO MODAL VIEW
public function get_activity_remainder(){
$process_month = $this->input->post('process_month');
$mode_qry = 'select prime_remainder_mode_id,remainder_mode_value from cw_remainder_mode where cw_remainder_mode.trans_status = 1';
$mode_qry_info = $this->db->query("CALL sp_a_run ('SELECT','$mode_qry')");
$mode_qry_result = $mode_qry_info->result();
$mode_qry_info->next_result();
foreach($mode_qry_result as $mode_rslt){
$prime_remainder_mode_id = $mode_rslt->prime_remainder_mode_id;
$remainder_mode_value = $mode_rslt->remainder_mode_value;
$status_line .="<option value='$prime_remainder_mode_id' $selected>$remainder_mode_value</option>";
}
$qry = 'select prime_activity_remainder_id,apply_month,cw_activity_remainder.employee_code as employee_code,remainder_status,cw_employees.emp_name as emp_name,remainder_details from cw_activity_remainder inner join cw_employees on cw_employees.employee_code = cw_activity_remainder.employee_code where remainder_status = 1 and cw_activity_remainder.trans_status = 1 and apply_month ="'.$process_month.'"';
$activity_rem_info = $this->db->query("CALL sp_a_run ('SELECT','$qry')");
$activity_rem_result = $activity_rem_info->result();
$activity_rem_info->next_result();
$activity_rem_count = $activity_rem_info->num_rows();
foreach($activity_rem_result as $rslt){
$apply_month = $rslt->apply_month;
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$remainder_status = $rslt->remainder_status;
$remainder_details = $rslt->remainder_details;
if($remainder_status === 1){
$selected = "selected";
}
$active_id = $rslt->prime_activity_remainder_id;
$activity_line .= "<tr>
<td>$apply_month</td>
<td>$employee_code</td>
<td>$emp_name</td>
<td>$remainder_details</td>
<td><select name='remainder_status[]' id='remainder_status_$active_id'>
$status_line
</select></td>
<td>
<a class='btn btn-primary btn-sm' onclick='edit_active_info($active_id)'>Submit</a>
</td>
</tr>";
}
$table_data = "<table class='table table-striped table-bordered' id='activity_rem_table'>
<thead>
<tr>
<th scope='col'>Apply Month</th>
<th scope='col'>Employee Code</th>
<th scope='col'>Employee Name</th>
<th scope='col'>Remainder Details</th>
<th scope='col'>Remainder status</th>
<th scope='col'>Action</th>
</tr>
</thead>
<tbody>
$activity_line
</tbody>
</table>";
echo json_encode(array("success" => TRUE,'table_data' => $table_data,'sts_count'=>$activity_rem_count));
}
public function inc_create_formula_file(){
//file path with server
$filename = dirname(__FILE__)."/"."Increment_calculation_model.php";
$filename = str_replace('controllers','models',$filename);
$can_process = false;
$input_query = 'SELECT line_input_bind_table,line_input_bind_to,line_input_bind_col,condition_check_form FROM `cw_form_bind_input` INNER JOIN `cw_form_condition_formula` ON cw_form_condition_formula.prime_cond_id = cw_form_bind_input.input_cond_id
WHERE input_cond_module_id ="employees" AND cond_order != 0 AND line_input_bind_col !="" AND cw_form_condition_formula.trans_status=1';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$input_colum = "";
$formula_code = "";
foreach($input_result as $input){
$role = $input->line_input_bind_table;
$out_colum = $input->line_input_bind_to;
$input_colum = $input->line_input_bind_col;
$condition_check_form = $input->condition_check_form;
$condition_check_form = explode(",",$condition_check_form);
if($input_colum){
foreach($condition_check_form as $check_form){
if(strpos($input_colum,"@$check_form@") !== false){
$value = "\$trans['".$check_form."']";
$input_colum = str_replace("@$check_form@",$value, $input_colum);
$input_colum = str_replace("return","\$trans['".$out_colum."'] = ", $input_colum);
$data .= "'$check_form' => $value,";
}
}
}
$formula_code .= "\n\t\t\t $input_colum \n\t\t\t";
}
$oldmask = umask(0);
$fname = 'increment_calculation($trans_array,$employee_code){';
//$emp_code = "\n\t\t\t".'$employee_code = "'.$employee_code.'";';
$formula_code = "\n<?php class Increment_calculation_model extends CI_Model{\n\tpublic function $fname \n\t\t".' foreach($trans_array as $trans){ '.$formula_code."\n\t\t \$trans_array[\$employee_code] = \$trans;\n\t\t } return \$trans_array; \n\t\t }\n}?>";
$formula_temp_file = dirname(__FILE__)."/"."Increment_calculation_model.php";
$formula_temp_file = str_replace('controllers','models',$formula_temp_file);
fopen("$formula_temp_file", "w");
file_put_contents("$formula_temp_file",$formula_code);
chmod($formula_temp_file, 0777);
umask($oldmask);
}
//EDIT ACTIVITY REMINDER INFO
public function edit_active_info(){
$active_id = $this->input->post('prime_activity_remainder_id');
$remainder_status = $this->input->post('remainder_status');
$logged_id = $this->session->userdata('logged_id');
$update_date = date("Y-m-d H:i:s");
$upd_qry = 'remainder_status = "'.$remainder_status.'"';
$update_qry .= 'trans_updated_by = "'.$logged_id.'", trans_updated_date = "'.$update_date.'"';
$upd_inc_query = 'UPDATE cw_activity_remainder SET '.$upd_qry.' WHERE prime_activity_remainder_id = "'.$active_id.'"';
if($this->db->query("CALL sp_a_run ('RUN','$upd_inc_query')")){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Updated",'prime_activity_remainder_id'=> $active_id ));
}else{
echo json_encode(array('success' => FALSe, 'message' => "Update failed, Please Try Again"));
}
}
//sorting formula updates
public function sort_formula($formula_for,$out_column,$payroll_formula,$formula_mode){
$preg_match_inputs = preg_match_all('#\@(.*?)\@#', $payroll_formula,$preg_match_inputsvalue);
$preg_match_inputsvalue_count = count($preg_match_inputsvalue[1]);
$input_match_column = implode('","',$preg_match_inputsvalue[1]);
$input_match_column ='"'.$input_match_column.'"';
$qu_find_sortorder='select IFNULL(MIN(formula_order), 0) as formula_order_min, IFNULL(MAX(formula_order), 0) as formula_order_max from cw_payroll_formula where formula_for = "'.$formula_for.'" and (out_column="'.$out_column.'" or out_column in ('.$input_match_column.')) and trans_status = 1 order by formula_order desc';
$max_min_data = $this->db->query("CALL sp_a_run ('SELECT','$qu_find_sortorder')");
$max_min_rslt = $max_min_data->result();
$max_min_data->next_result();
$outcolum_maxorder = $max_min_rslt[0]->formula_order_max;
$outcolum_minorder = $max_min_rslt[0]->formula_order_min;
$outcolum_order = $outcolum_maxorder;
$min = $outcolum_minorder;
$qu_find_sortorder_data='select out_column,formula_order from cw_payroll_formula where formula_for = "'.$formula_for.'" and (formula_mode !=1 or out_column="'.$out_column.'") and formula_order between "'.$min.'" and "'.$outcolum_maxorder.'" and trans_status = 1 order by formula_order asc';
$max_min_sort_data = $this->db->query("CALL sp_a_run ('SELECT','$qu_find_sortorder_data')");
$max_min_sort_rslt = $max_min_sort_data->result();
$max_min_sort_data->next_result();
foreach ($max_min_sort_rslt as $result){
$out_column_db = $result->out_column;
$formula_order = $result->formula_order;
if($out_column==$out_column_db){
if((int)$formula_mode === 1){
$upd_sort = 'UPDATE cw_payroll_formula SET formula_order = 1 where formula_for = "'.$formula_for.'" and out_column = "'.$out_column.'" and trans_status = 1';
}else{
$upd_sort = 'UPDATE cw_payroll_formula SET formula_order = "'.$outcolum_order.'" where formula_for = "'.$formula_for.'" and out_column = "'.$out_column.'" and trans_status = 1';
}
$this->db->query("CALL sp_a_run ('RUN','$upd_sort')");
}else{
$upd_sort = 'UPDATE cw_payroll_formula SET formula_order = "'.$min.'" where formula_for = "'.$formula_for.'" and out_column="'.$out_column_db.'" and formula_order="'.$formula_order.'" and formula_mode !=1 and trans_status = 1';
$this->db->query("CALL sp_a_run ('RUN','$upd_sort')");
$min++;
}
}
if((int)$formula_mode !== 1){
$find_max_order_qry ='select IFNULL(MAX(formula_order), 0) as max_order from cw_payroll_formula where formula_for = "'.$formula_for.'" and trans_status = 1 and out_column != "net_pay" order by formula_order desc';
$max_order_data = $this->db->query("CALL sp_a_run ('SELECT','$find_max_order_qry')");
$max_order_rslt = $max_order_data->result();
$max_order_data->next_result();
$max_order = $max_order_rslt[0]->max_order;
$i = (int)$max_order + 1;
if($max_order){
$upd_sort_net = 'UPDATE cw_payroll_formula SET formula_order = "'.$i.'" where formula_for = "'.$formula_for.'" and out_column = "net_pay"';
$this->db->query("CALL sp_a_run ('RUN','$upd_sort_net')");
}
}
}
public function tax_process($trans_array,$employee_code,$process_month,$proof_wise_val,$tax_location,$role,$emp_age,$tax_data,$tax_calculation_id,$pf_match_formula,$pt_proj_amt_data,$pf_proj_formula,$earnings_result,$other_income_array,$hra_result,$section_array,$earnings_columns,$proj_combine,$gross_array,$formula_array,$fin_result,$professional_tax_db,$emp_proj_data,$tax_slab_result,$process_date,$actual_months,$month_count_rslt,$fin_set_id,$dec_emp_data,$proof_emp_data,$sec10_result,$pre_tax_data){
require_once "./dist/eos-1.0.0/eos.class.php";
$eq = new eqEOS();
$proj_month = date('m-Y', strtotime('+1 month', strtotime($process_date)));
/** Earning Part Start - BSK **/
$earned_array = array();
$total_proj = 0;
$gross_total = 0;
$total_earn = 0;
$pt_total = 0;
$pf_total = 0;
$gross_1 = 0;
$gross_2 = 0;
$gross_3 = 0;
$tax_insert_key = "";
$tax_insert_value = "";
$tax_update_qry = "";
$i = 0;
foreach ($earnings_columns as $key => $value){
$count = 0;
$total_actual = 0;
$pf_actual = 0;
$pt_actual = 0;
$total = 0;
$pf_match = "";
$projection_column = $proj_combine[$value];
$gross_section = $gross_array[$i];
foreach ($actual_months as $actual){
$month = $actual->format("m-Y");
$month_date = $actual->format("Y-m-d");
if(strtotime($month_date) < strtotime($process_date)){ //For Actual
$formula = str_replace('@', '', $formula_array[$i]);
if($projection_column){
$proj = $emp_proj_data[$projection_column];
$proj = $proj_result[0]->$projection_column;
}else{
$proj = 0;
}
if($formula){
$earn_comp = $value.'+'.($formula);
}else{
$earn_comp = $value;
}
if($professional_tax_db){
$earn_comp = $professional_tax_db.','.$earn_comp;
}else{
$earn_comp = $value;
}
if($pf_match_formula){
$pf_match = str_replace('@', '', $pf_match_formula);
$pf_match = ','.$pf_match.' AS pf_value';
}else{
$pf_match = "";
}
$earnings_query = 'SELECT '.$earn_comp.' AS "'.$value.'"'.$pf_match.' FROM cw_transactions WHERE cw_transactions.trans_status = 1 and transactions_month = "'.$month.'" and employee_code = "'.$employee_code.'"';
$earnings_data = $this->db->query("CALL sp_a_run ('SELECT','$earnings_query')");
$earnings_result = $earnings_data->result();
$earnings_data->next_result();
$act_val = $earnings_result[0]->$value;
$total_actual = $total_actual + $act_val;
}else{ //For Projection
$count++;
}
}
$total_proj = ($proj)*($count); //For Projection calc
$total = $total_actual + $total_proj; //total amount
$total_proj = number_format((float)$total_proj, 2, '.', '');
$total = number_format((float)$total, 2, '.', '');
$total_earn = $total_earn + $total;
if($gross_section === "1"){
$gross_1 = $gross_1 + $total_earn;
}elseif($gross_section === "2"){
$gross_2 = $gross_2 + $total_earn;
}else
if($gross_section === "3"){
$gross_3 = $gross_3 + $total_earn;
}
$i++;
}
//Other Income Process
$other_income_data = $other_income_array[$employee_code];
$other_income_val = 0;
foreach ($other_income_data as $other_income_val){
if($other_income_val){
$total_earn = $total_earn + $other_income_val;
}
}
$total_earn = number_format((float)$total_earn, 2, '.', '');
//Generate query value
$tax_insert_key .= "fin_set_id,emp_role,emp_code,process_month,earning_total,gross_17a,gross_17b,gross_17c";
$tax_insert_value = '"'.$fin_set_id.'","'.$role.'","'.$employee_code.'","'.$process_month.'","'.$total_earn.'","'.$gross_1.'","'.$gross_2.'","'.$gross_3.'"';
$tax_update_qry = 'emp_code = "'.$employee_code.'",process_month = "'.$process_month.'",earning_total = "'.$gross_total.'",gross_17a = "'.$gross_1.'",gross_17b = "'.$gross_2.'",gross_17c = "'.$gross_3.'"';
/** Earning Part END - BSK **/
//House rent Allowance
$hra_formula = $hra_result[0]->formula;
$actual_rent = $hra_result[0]->actual_rent_paid_percentage;
$pct_metro = $hra_result[0]->metro_percentage;
$non_metro = $hra_result[0]->non_metro_percentage;
$edu_cess = $hra_result[0]->education_cess_percentage;
$actual_rent_pct = $hra_result[0]->actual_rent_paid_percentage;
$earning_part_result = $this->earning_part();
/** SECTION 10 Part START - BSK **/
//Section 10 Part
$dec_value = 0;
$total_sec10 = 0;
$created_on = date("Y-m-d h:i:s");
foreach ($sec10_result as $key => $value){
$earnings = $value->earnings;
$tax_section_column = $value->tax_section_column;
$tax_subsection_column = $value->tax_subsection_column;
$tax_subsection_name = $value->tax_act_details;
$tax_subsection_limit = $value->tax_subsection_limit;
$sec10_tax_section = $value->tax_section;
$bill_required = $value->bill_required;
$exemption_method = $value->exemption_method;
//Check Data for get Whichever is Least
$earned_value = $earned_array[$earnings];
if((int)$proof_wise_val === 1){
$dec_value = $proof_emp_data[$tax_subsection_column];
}else{
$dec_value = $dec_emp_data[$tax_subsection_column];
if(!$dec_value){
$dec_value = "0.00";
}
}
//HRA CALCUALTIONS FOR ALL
if($tax_subsection_column === 'taxsubsec_1'){
$earned_hra = $earned_array[$earnings];
if((int)$proof_wise_val === 1){
$house_rent = $proof_emp_data['tax_house_rent'];
}else{
$house_rent = $dec_emp_data['tax_house_rent'];
}
//Get declaration House Rent entry Value
if($house_rent === "0.00" || !$house_rent && $proof_wise_val === 0){
$hr_query = 'SELECT sum(metro_rent_paid)+sum(non_metro_rent_paid) as house_rent FROM `cw_house_rent_entry` WHERE effective_month = "'.$process_month.'" and trans_status = 1 and employee_code = "'.$employee_code.'"';
$hr_data = $this->db->query("CALL sp_a_run ('SELECT','$hr_query')");
$hr_result = $hr_data->result();
$hr_data->next_result();
$house_rent = $dec_result[0]->house_rent;
}
$hra_formula = $hra_result[0]->formula;
$preg_match = preg_match_all('#\@(.*?)\@#', $hra_formula, $match);
foreach($match[1] as $for_rslt){
$find_value = "@$for_rslt@";
$for_value = $earned_array[$for_rslt];
$hra_formula = str_replace($find_value,$for_value,$hra_formula);
}
if($hra_formula){
$hra_formula = $eq->solveIF($hra_formula);
}
if($hra_formula){
$hra_res = $hra_formula;
}else{
$hra_res = 0;
}
if($house_rent){
$actual_rent = $hra_res*($actual_rent/100);
$rent = $house_rent - $actual_rent;
$metro_pct = 0;
if((int)$metro === 1){
$metro_rslt = $hra_formula*($pct_metro/100);
$metro_pct = $pct_metro;
}else{
$metro_rslt = $hra_formula*($non_metro/100);
$metro_pct = $non_metro;
}
if($rent > 0){
$sec10_least_value = min($earned_hra, $metro_rslt, $rent);
//$sec10_upd_query .= 'hra = "'.$sec10_least_value.'",';
$tax_insert_key .= ",hra";
$tax_insert_value .= ',"'.$sec10_least_value.'"';
$tax_update_qry .= ',hra = "'.$sec10_least_value.'"';
}
}
}else{
if((int)$bill_required === 1){
$sec10_least_value = min($earned_value, $tax_subsection_limit,$dec_value);
}else{
$sec10_least_value = min($earned_value, $tax_subsection_limit);
$dec_value = number_format((float)$earned_value, 2, '.', '');
}
$tax_insert_key .= ','.$tax_subsection_column;
$tax_insert_value .= ',"'.$sec10_least_value.'"';
$tax_update_qry .= ','.$tax_subsection_column.' = "'.$sec10_least_value.'"';
}
$total_sec10 = $total_sec10 + $sec10_least_value;
$total_sec10 = number_format((float)$total_sec10, 2, '.', '');
$tax_insert_key .= ',tot_'.$tax_section_column;
$tax_insert_value .= ',"'.$total_sec10.'"';
$tax_update_qry .= ',tot_'.$tax_section_column.' = "'.$total_sec10.'"';
}
$gross_total = $total_earn - $total_sec10;
$gross_total = number_format((float)$gross_total, 2, '.', '');
$tax_insert_key .= ',gross_tot_taxsec_1';
$tax_insert_value .= ',"'.$gross_total.'"';
$tax_update_qry .= ',gross_tot_taxsec_1 = "'.$gross_total.'"';
/** SECTION 10 Part END - BSK **/
/** SUB SECTIONS Part START - BSK **/
$sub_section_upd_query = "";
$gross_upd_query = "";
foreach ($section_array as $key => $sub_section){
$tot_limit = 0;
$tot_deduct = 0;
$total_sub = 0;
foreach ($sub_section as $sub => $sub_value){
$tax_section_column = $sub_value['tax_section_column'];
$tax_sub_section_id = $sub_value['prime_tax_sub_section_id'];
$tax_subsection_column = $sub_value['tax_subsection_column'];
$tax_subsection_name = $sub_value['tax_act_details'];
$tax_subsection_limit = $sub_value['tax_subsection_limit'];
$tax_section = $sub_value['tax_section'];
$bill_required = $sub_value['bill_required'];
$section_limit = $sub_value['section_limit'];
//Get declaration entry Value
$dec_value = $dec_emp_data[$tax_subsection_column];
$sub_least_value = min($dec_value, $tax_subsection_limit);
$tax_insert_key .= ','.$tax_subsection_column;
$tax_insert_value .= ',"'.$sub_least_value.'"';
$tax_update_qry .= ','.$tax_subsection_column.' = "'.$sub_least_value.'"';
if($tax_subsection_column === "taxsubsec_8"){
$total_sub = $total_sub + $tax_subsection_limit;
}else
if($tax_subsection_column === "taxsubsec_10"){
/** Get PT Projection value START **/
$pt_proj_amt = $pt_proj_amt_data[$employee_code];
$pt_proj = $this->get_pt_projection($pt_proj_amt,$tax_location,$process_month,$count,$employee_code,$professional_tax_db);
$pt_proj = number_format((float)$pt_proj, 2, '.', '');
/** Get PT Projection value END **/
$pt_total = $pt_actual + $pt_proj;
$pt_total = number_format((float)$pt_total, 2, '.', '');
$total_sub = $total_sub + $pt_total;
$total_sub = number_format((float)$total_sub, 2, '.', '');
}else{
//Check Secsion Matching is Exist
$section_match_query = 'SELECT formula FROM `cw_section_matching` WHERE tax_sub_section = "'.$tax_sub_section_id.'" and trans_status = 1';
$section_match_data = $this->db->query("CALL sp_a_run ('SELECT','$section_match_query')");
$section_match_result = $section_match_data->result();
$section_match_data->next_result();
$match_formula = $section_match_result[0]->formula;
if($match_formula){
$preg_match = preg_match_all('#\@(.*?)\@#', $match_formula, $match);
foreach($match[1] as $for_rslt){
$find_value = "@$for_rslt@";
$for_value = $trans_array[$employee_code][$for_rslt];
$match_formula = str_replace($find_value,$for_value,$match_formula);
}
if($match_formula){
$match_value = $eq->solveIF($match_formula);
}
$sub_least_value = min($match_value, $tax_subsection_limit);
if($tax_subsection_column === "taxsubsec_11"){
/** Get PF Projection value START **/
$pf_proj = $this->get_pf_projection($pf_proj_formula,$count,$employee_code);
/** Get PF Projection value END **/
$pf_total = $pf_actual + $pf_proj;
$pf_total = number_format((float)$pf_total, 2, '.', '');
$tot_deduct = $tot_deduct + $pf_total;
$tot_deduct = number_format((float)$tot_deduct, 2, '.', '');
//$total_sub = $total_sub + $pf_total;
}
}else{
$total_sub = $total_sub + $sub_least_value;
$tot_limit = $tot_limit + $tax_subsection_limit;
$tot_deduct = $tot_deduct + $dec_value;
}
}
}
if($key === "Under Section 80C"){
$total_sub = min($tot_deduct, $section_limit);
$total_sub = number_format((float)$total_sub, 2, '.', '');
}
$tax_insert_key .= ',tot_'.$tax_section_column;
$tax_insert_value .= ',"'.$total_sub.'"';
$tax_update_qry .= ',tot_'.$tax_section_column.' = "'.$total_sub.'"';
$gross_total = $gross_total - $total_sub;
$gross_total = number_format((float)$gross_total, 2, '.', '');
$tax_insert_key .= ',gross_tot_'.$tax_section_column;
$tax_insert_value .= ',"'.$gross_total.'"';
$tax_update_qry .= ',gross_tot_'.$tax_section_column.' = "'.$gross_total.'"';
}
// ADD / Less other income details updates
$other_income_qry = 'select cw_income_type.income_type,cw_other_income.income_type as income_type_id,income_description,other_income_column from cw_other_income inner join cw_income_type on cw_income_type.prime_income_type_id =cw_other_income.income_type where cw_other_income.trans_status = 1 and (cw_other_income.income_type =1 or cw_other_income.income_type =2) order by cw_income_type.income_type';
$other_income_info = $this->db->query("CALL sp_a_run ('SELECT','$other_income_qry')");
$other_income_result = $other_income_info->result();
$other_income_info->next_result();
$income_val = 0;
$tot_add = 0;
$tot_less = 0;
foreach ($other_income_result as $income_key => $income_value){
$income_type_id = $income_value->income_type_id;
$income_type = $income_value->income_type;
$income_description = $income_value->income_description;
$other_income_column = $income_value->other_income_column;
$other_income_column = $income_value->other_income_column;
$other_income_entry_query = 'SELECT '.$other_income_column.' FROM cw_other_income_entry WHERE cw_other_income_entry.trans_status = 1 and effective_month = "'.$process_month.'" and emp_code = "'.$employee_code.'"';
$other_income_entry_data = $this->db->query("CALL sp_a_run ('SELECT','$other_income_entry_query')");
$other_income_entry_result = $other_income_entry_data->result();
$other_income_entry_data->next_result();
$income_val = $other_income_entry_result[0]->$other_income_column;
$tax_insert_key .= ','.$other_income_column;
$tax_insert_value .= ',"'.$income_val.'"';
$tax_update_qry .= ','.$other_income_column.' = "'.$income_val.'"';
if((int)$income_type_id === 1){
$tot_add = $tot_add + $income_val;
}else{
$tot_less = $tot_less + $income_val;
}
}
if($tot_add){
$gross_total = $gross_total + $tot_add;
}
if($tot_less){
$gross_total = $gross_total - $tot_less;
}
$gross_total = number_format((float)$gross_total, 2, '.', '');
$gross_total = ceil($gross_total / 10) * 10; //10 Rupee Rounding
$final_value = number_format((float)$gross_total, 2, '.', '');
$tax_insert_key .= ',tot_other,final_gross';
$tax_insert_value .= ',"'.$gross_total.'","'.$final_value.'"';
$tax_update_qry .= ',tot_other = "'.$gross_total.'",final_gross = "'.$final_value.'"';
/** SUB SECTIONS Part END - BSK **/
/** TAX SLAB Part START - BSK **/
$total_tax = 0;
$tax_value = 0;
foreach ($tax_slab_result as $tax => $tax_slab){
$min_age = $tax_slab->min_age;
$max_age = $tax_slab->max_age;
$tax_range_start = $tax_slab->tax_range_start;
$tax_range_end = $tax_slab->tax_range_end;
$tax_amount = $tax_slab->tax_amount;
$slap_type = $tax_slab->prime_slap_type_id;
if($min_age <= $emp_age && $max_age >= $emp_age){
//Check Value Inside the range
if((int)$tax_range_start === 0 && (int)$tax_amount === 0){
if($final_value > $tax_range_end){
$final_tax = $final_value - $tax_range_end;
}else{
$final_tax = 0;
}
}else{
if((int)$final_tax > 0 && $final_value >= $tax_range_start && $final_value >= $tax_range_end){
$tax_value = ($tax_range_start * $tax_amount)/100;
$final_tax = $final_tax - $tax_range_start;
}else
if((int)$final_tax > 0 && $final_value >= $tax_range_start && $final_value <= $tax_range_end){
$tax_value = ($final_tax * $tax_amount)/100;
$final_tax = 0;
}else{
$tax_value = 0;
}
}
}
}
$total_tax = $total_tax + $tax_value;
$total_tax = round($total_tax);
$total_tax = number_format((float)$total_tax, 2, '.', '');
$margin_relief = $this->margin_relief_amt($final_value,$total_tax);
$margin_relief_line = "";
$surcharge_pct_line = "";
if(!empty($margin_relief)){
$surcharge_pct = $margin_relief['surcharge_pct'];
$surcharge_charge = $margin_relief['surcharge_charge'];
$margin_relief_amt = $margin_relief['margin_relief'];
if((int)$surcharge_pct !== 0){
if((int)$margin_relief_amt !==0){
$tax_insert_key .= ',margin_relief';
$tax_insert_value .= ',"'.$margin_relief_amt.'"';
$tax_update_qry .= ',margin_relief = "'.$margin_relief_amt.'"';
}
$tax_insert_key .= ',surcharge_amt';
$tax_insert_value .= ',"'.$surcharge_charge.'"';
$tax_update_qry .= ',surcharge_amt = "'.$surcharge_charge.'"';
}
}
/** TAX SLAB Part END - BSK **/
//Less:87A of the Income Tax Act, 1961 rebate Tax refund calculation
$refund_sts = 0;
if((int)$final_value <= 500000){
$refund_sts = 1;
}
//education Cess Calculation
if($total_tax){
if((int)$refund_sts === 1){
$cess_value = 0;
$total_tax = 0;
}else{
if((int)$surcharge_charge !==0){
$total_tax = $total_tax + $surcharge_charge;
$cess_value = round(($total_tax * $edu_cess)/100);
}else{
$cess_value = round(($total_tax * $edu_cess)/100);
}
$cess_value = number_format((float)$cess_value, 2, '.', '');
}
//Tax Payable including Education Cess minus of Relief under section 89
//with education cess percentage
$cess_tax = $total_tax + $cess_value - $margin_relief_amt;
$cess_tax = number_format((float)$cess_tax, 2, '.', '');
}
$month_tds_val = 0;
$final_pay_tax = 0;
$prev_tax_line = "";
$tax_pay_line = "";
//finding previous tax calculation values// map tds or default values is monthly tds
//Tax financial setting date details
$prev_tax = 0;
if($pre_tax_data){
$prev_tax = $pre_tax_data[$employee_code]['prev_tax'];
}
if((int)$refund_sts === 0){
$final_tax_pay_month = $cess_tax - $prev_tax;
$month_tds_val = round($final_tax_pay_month/$month_count_rslt);
}
$final_tax_pay_month = number_format((float)$final_tax_pay_month, 2, '.', '');
$month_tds_val = number_format((float)$month_tds_val, 2, '.', '');
//}
if((int)$final_value <= 500000){
$tax_insert_key .= ',rebate_amt';
$tax_insert_value .= ',"'.$total_tax.'"';
$tax_update_qry .= ',rebate_amt = "'.$total_tax.'"';
}
$tax_insert_key .= ',slab_type,total_tax,tax_amt,final_tax_amt,edu_cess';
$tax_insert_value .= ',"'.$slap_type.'","'.$final_value.'","'.$cess_tax.'","'.$month_tds_val.'","'.$cess_value.'"';
$tax_update_qry .= ','.$other_income_column.' = "'.$income_val.'",tax_amt = "'.$cess_tax.'",final_tax_amt = "'.$month_tds_val.'",edu_cess = "'.$cess_value.'"';
//Update TDS
$trans_array[$employee_code]['monthly_tds'] = $month_tds_val;
return $trans_array;
}
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;
}
public function check_import_month(){
$transaction_month = $this->input->post("transaction_month");
$role = $this->input->post("category");
$payroll_exist_qry = 'select count(*) as exist_count from cw_transactions where cw_transactions.trans_status =1 and cw_transactions.termination_status = 0 and role = "'.$role.'" and transactions_month = "'.$transaction_month.'"';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('SELECT','$payroll_exist_qry')");
$payroll_exist_rslt = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$exist_count = $payroll_exist_rslt[0]->exist_count;
if((int)$exist_count === 0){
echo json_encode(array("success" => TRUE,'message' => 'Ok Proceed'));
}else{
echo json_encode(array("success" => FALSE,'message' => 'Payroll Already Exist This Month'));
}
}
}
?>