File: /home/cafsindia/.trash/application.1/models/Income_tax_process_model.php
<?php
class Income_tax_process_model extends CI_Model{
//FUNCTION FOR COMMON(INCOME TAX PROCESS AND PROCESS PAYROLL)
public function income_tax_with_process_payroll($process_month,$annexure_sts,$process_type,$process_emp_code,$process_role,$process_mode,$proof_wise,$month_name,$logged_id,$today_date,$process_from,$pro_income_tax_type){
$financial_info = $this->get_financial_year();
$fin_set_id = $financial_info[0]->prime_financial_setting_id;
$start_date = $financial_info[0]->start_date;
$end_date = $financial_info[0]->end_date;
$new_sd = $financial_info[0]->new_sd;
$start_month = date("m-Y",strtotime($start_date));
$end_month = date("m-Y",strtotime($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,$end_date);
require_once "./dist/eos-1.0.0/eos.class.php";
$eq = new eqEOS();
//PROCESS MONTH SHOULD BE WITHIN THE FINANCIAL SETTING START AND END DATE -> NB[03-06-2024]
$fin_start = date('Y-m-d', strtotime('01-' . $start_month));
$fin_end = date('Y-m-d', strtotime('01-' . $end_month));
if(!$fin_set_id){
echo json_encode(array('success' => false, 'message' => 'Financial Settings not Exist..'));
exit(0);
}
if(!($process_date >= $fin_start && $process_date <= $fin_end)){ //Restrict If Other Fin Year Activated
echo json_encode(array('success' => false, 'message' => 'Check your Active Financial Year Start and End date.'));
exit(0);
}
//Process Tax mode =1 and mode 2 delete the tax only delete cal
$proof_wise_val = 0;
if($proof_wise === "on"){
$proof_wise_val = 1;
}
//Check Incometax lock exist
$exist_qry = 'select count(prime_income_tax_lock_id) as income_tax_count from cw_income_tax_lock where tax_month = "'.$process_month.'" and income_tax_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]->income_tax_count;
if((int)$num_rows > 0){
echo json_encode(array('success' => false, 'message' => 'Income Tax Locked for this Month'));
exit(0);
}
//Check payroll lock exist
$payroll_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';
$payroll_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_exist_qry')");
$payroll_exist_result = $payroll_exist_data->result();
$payroll_exist_data->next_result();
$num_rows = $payroll_exist_result[0]->payroll_count;
if((int)$num_rows > 0){
echo json_encode(array('success' => false, 'message' => 'Payroll Locked for this Month'));
exit(0);
}
$process_dt = new DateTime("01-$process_month 00:00:00");
$process_dt->modify('-1 month');
$next_month = $process_dt->format("Y-m-d");
$process_month_only = '01-'.$process_month;
$process_month_only = date('Y-m-d',strtotime($process_month_only));
$where_query = "";
$cond_query = "";
$section_dec_query = "";
$off_tds_qry = "";
$tax_where_query = "";
$off_where_query = "";
$fandf_where = "";
if ($process_type === 1){
$where_query = ' and employee_code = "' . $process_emp_code . '" ';
$trans_query = ' and employee_code = "' . $process_emp_code . '" ';
$cond_query = ' and cw_employees.employee_code = "' . $process_emp_code . '" ';
$tax_where_query = ' and emp_code = "' . $process_emp_code . '" ';
$off_where_query = ' and employee_code = "' . $process_emp_code . '" ';
}else if ($process_type === 2){
$where_query = ' and role in ( ' . $process_role . ')';
$trans_query = ' and role in ( ' . $process_role . ')';
$cond_query = ' and cw_employees.role in (' . $process_role . ') ';
$tax_where_query = ' and category in ( ' . $process_role . ') ';
$off_where_query = ' and category in ( ' . $process_role . ') ';
}
$process_month_dt = "01-".$process_month;
$table_name = 'cw_tax_calculation';
if($process_from === 'projection_tax_process'){
$process_date = date('Y-m-d', strtotime('-1 month', strtotime($process_date)));
$table_name = 'cw_tax_proj_calculation';
//Check payroll exist
$istrans_exist_qry = 'select count(*) as result_count from cw_transactions where trans_status = 1 and transactions_month = "'.$process_month.'" and fandf = 2'.$trans_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){
echo json_encode(array('success' => false, 'msg' => "Payroll Processed for this Month.. Please process the Income tax Process..!"));
exit(0);
}
}
//Check payroll exist
if($process_from === 'fandf_process_payroll'){
$fandf_sts = 'and fandf = 1';
}else{
$fandf_sts = 'and fandf = 2';
}
$istrans_exist_qry = 'select count(*) as result_count from cw_transactions where trans_status = 1 and transactions_month = "'.$next_month.'" '. $fandf_sts.' '.$trans_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){
echo json_encode(array('success' => false, 'message' => "Payroll Processed for Next Month..!"));
exit(0);
}
//Check Previous Company TDS
$prev_comp_tds_qry = 'select IFNULL(previous_tax,0) as previous_tax,employee_code from cw_previous_company_income where financial_setting_id = "'.$fin_set_id.'" and trans_status = 1';
$prev_comp_tds_data = $this->db->query("CALL sp_a_run ('SELECT','$prev_comp_tds_qry')");
$prev_comp_tds_result = $prev_comp_tds_data->result_array();
$prev_comp_tds_data->next_result();
if($prev_comp_tds_result){
foreach($prev_comp_tds_result as $arr){
$prev_comp_tds_result_array[$arr['employee_code']] = $arr;
}
}
$off_tds_qry = 'SELECT SUM(amount) as amount,employee_code,category from cw_offline_tds_entry where payroll_month = "'.$process_month.'" and trans_status = 1 '.$off_where_query.'';
$off_tds_info = $this->db->query("CALL sp_a_run ('SELECT','$off_tds_qry')");
$off_tds_result = $off_tds_info->result_array();
$off_tds_info->next_result();
if($off_tds_result){
foreach($off_tds_result as $arr){
$off_tds_result_array[$arr['employee_code']] = $arr;
}
}
if($process_from === 'projection_tax_process'){
if($start_month === $process_month){
//Get Employee Query
$emp_info_query = 'SELECT cw_category.category_name,cw_employees.role,cw_employees.metro,cw_employees.employee_code,cw_employees.emp_age,cw_employees.emp_name,cw_position.position_name,cw_employees.professional_tax_location,cw_employees.income_tax_type,cw_employees.date_of_joining,cw_employees.termination_status FROM cw_employees left JOIN cw_position on cw_position.prime_position_id = cw_employees.designation left join cw_category on cw_category.prime_category_id = cw_employees.role
where (cw_employees.date_of_joining <= "' . $end_date . '" and cw_employees.termination_status = 0 or cw_employees.last_working_date >= "' . $start_date . '" cw_employees.termination_status = 1) '.$where_query;
}else{
$emp_info_query = 'SELECT cw_category.category_name,cw_employees.role,cw_employees.metro,cw_employees.employee_code,cw_employees.emp_age,cw_employees.emp_name,cw_position.position_name,cw_employees.professional_tax_location,cw_employees.income_tax_type,cw_employees.date_of_joining,cw_employees.termination_status FROM cw_transactions as a inner join cw_employees on cw_employees.employee_code = a.employee_code left JOIN cw_position on cw_position.prime_position_id = cw_employees.designation left join cw_category on cw_category.prime_category_id = cw_employees.role INNER JOIN (SELECT employee_code, date_format(MAX(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_transactions WHERE date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") and termination_status=0 ' .$where_query. ' GROUP BY employee_code) as groupedtt ON a.employee_code = groupedtt.employee_code AND a.transactions_month = groupedtt.max_date';
}
}else{
if($end_month === $process_month){
$emp_info_query = 'SELECT cw_category.category_name,cw_employees.role,cw_employees.metro,cw_employees.employee_code,cw_employees.emp_age,cw_employees.emp_name,cw_position.position_name,cw_employees.professional_tax_location,cw_employees.income_tax_type,cw_employees.date_of_joining,cw_employees.termination_status FROM cw_transactions as a inner join cw_employees on cw_employees.employee_code = a.employee_code left JOIN cw_position on cw_position.prime_position_id = cw_employees.designation left join cw_category on cw_category.prime_category_id = cw_employees.role INNER JOIN (SELECT employee_code, date_format(MAX(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_transactions WHERE date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y"), "%Y-%m-%d") >= "'.$start_date.'" and date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y"), "%Y-%m-%d") <= "'.$end_date.'" ' .$where_query. ' GROUP BY employee_code) as groupedtt ON a.employee_code = groupedtt.employee_code AND a.transactions_month = groupedtt.max_date';
}else{
$emp_info_query = 'SELECT cw_category.category_name,cw_employees.role,cw_employees.metro,cw_employees.employee_code,cw_employees.emp_age,cw_employees.emp_name,cw_position.position_name,cw_employees.professional_tax_location,cw_employees.income_tax_type,cw_employees.date_of_joining,cw_employees.termination_status FROM cw_employees left JOIN cw_position on cw_position.prime_position_id = cw_employees.designation left join cw_category on cw_category.prime_category_id = cw_employees.role INNER JOIN cw_transactions on cw_employees.employee_code = cw_transactions.employee_code WHERE cw_employees.trans_status = 1 and cw_transactions.process_month = "'.$process_month.'" and cw_transactions.trans_status = 1 and cw_employees.role != 1' . $cond_query . ' order by cw_employees.employee_code';
}
}
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_info_query')");
$emp_rslt = $emp_data->result();
$emp_data->next_result();
$del_query = "";
if((int)$process_mode === 2){ // Delete Tax
if($process_type === 1){
$del_query = 'and emp_code = "' . $process_emp_code . '"';
}else if($process_type === 2){
$del_query = 'and emp_role = "' . $process_role . '"';
}
//Get Employee Query
if($process_from === 'fandf_process_payroll'){
$fandf_where = 'and cw_transactions.fandf=1';
}else{
$fandf_where = "and cw_transactions.fandf=2";
}
$delete_ins_query = 'INSERT INTO cw_tax_calculation_dlt SELECT *,"'. $this->logged_id .'" as trans_deleted_by,"'.$today_date.'" as trans_deleted_date FROM cw_tax_calculation WHERE trans_status = 1 and process_month = "' . $process_month . '"' . $del_query;
$save_result = $this->db->query("CALL sp_a_run ('RUN','$delete_ins_query')");
$prime_dlt_data = 'trans_deleted_by = "'. $this->logged_id .'",trans_deleted_date = "'.$today_date.'"';
$prime_dlt_query = 'UPDATE cw_tax_calculation_dlt SET '. $prime_dlt_data .' WHERE trans_status = 1 and process_month = "' . $process_month . '"' . $del_query;
$this->db->query("CALL sp_a_run ('UPDATE','$prime_dlt_query')");
if($save_result){
$tax_delete_query = 'DELETE from cw_tax_calculation WHERE trans_status = 1 and process_month = "' . $process_month . '"' . $del_query;
$this->db->query("CALL sp_a_run ('RUN','$tax_delete_query')");
}
echo json_encode(array(
'success' => false,
'message' => "Data deleted successfully!"
));
exit(0);
}else{
//Validation Checking
//Section 10 Part
$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,cw_tax_sub_section.income_tax_type from `cw_income_matching` inner join cw_tax_sub_section on cw_tax_sub_section.tax_subsection_column = cw_income_matching.excemption_component inner join cw_tax_section on cw_tax_section.prime_tax_section_id = cw_tax_sub_section.tax_section and cw_income_matching.financial_setting_id = '.$fin_set_id.' and cw_tax_sub_section.financial_setting_id = '.$fin_set_id.' where cw_income_matching.trans_status = 1 and cw_tax_sub_section.trans_status = 1 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();
if (!$sec10_result){
echo json_encode(array(
'success' => False,
'message' => "Section 10 Excemption Details not Mapped..!"
));
exit(0);
}
//Dynamic column list array
$get_column_qry = 'select * from cw_payroll_function_map where trans_status=1';
$get_column_data = $this->db->query("CALL sp_a_run ('SELECT','$get_column_qry')");
$get_column_result = $get_column_data->result();
$get_column_data->next_result();
$monthly_tds_db = "";
$one_time_tds_db = "";
foreach ($get_column_result as $column_name){
$loc_name = $column_name->loc_name;
$db_column = $column_name->db_column;
if ($loc_name == "professional_tax_amount"){
$professional_tax_db = $db_column;
}else
if ($loc_name == "fixed_pf"){
$fixed_pf_db = $db_column;
}else
if ($loc_name == "pt_gross"){
$pt_gross_db = $db_column;
}else
if ($loc_name == "one_time_tds"){
$one_time_tds_db = $db_column;
}else
if($loc_name == "monthly_tds"){
$monthly_tds_db = $db_column;
}
}
if($professional_tax_db === ''){
echo json_encode(array('success'=> False,'message'=> "Ptax Column not Mapped.."));
exit(0);
}
if($fixed_pf_db === ''){
echo json_encode(array('success'=> False,'message'=> "Fixed PF Column not Mapped.."));
exit(0);
}
if($monthly_tds_db === "" || $one_time_tds_db === ""){
echo json_encode(array('success'=> False,'message'=> "Monthly TDS and One time TDS Columns are not Mapped.."));
exit(0);
}
$pf_match_query = 'SELECT formula FROM `cw_section_matching` WHERE tax_sub_section = "taxsubsec_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;
//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 AND cw_form_setting.prime_module_id = "employees" and financial_setting_id = '.$fin_set_id.' order by order_no';
$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 cw_other_income.income_type,income_description,other_income_column,annual_limit 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 in (3,4,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();
//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 prime_financial_setting_id = "'.$fin_set_id.'"';
$hra_data = $this->db->query("CALL sp_a_run ('SELECT','$hra_query')");
$hra_result = $hra_data->result();
$hra_data->next_result();
if(empty($hra_result)){
echo json_encode(array(
'success' => false,
'message' => "Please Check your Financial Settings.."
));
exit(0);
}
$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;
//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,cw_tax_sub_section.income_tax_type 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_section.tax_section_column != "taxsec_1" AND cw_tax_sub_section.trans_status = 1 and cw_tax_sub_section.financial_setting_id = '.$fin_set_id.' 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();
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,
"income_tax_type" => $value->income_tax_type
);
}
//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 and financial_setting_id = '.$fin_set_id.' AND cw_form_setting.prime_module_id = "employees" order by order_no 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);
$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_combine = array_combine($proj_earnings, $projection_columns);
$column_qry = 'SELECT label_name,field_type FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND input_view_type IN (1,2) and transaction_type in (2,3) 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();
$columns = array_column($column_rslt, 'label_name');
$column_label = implode(',', $columns);
if($process_from === 'projection_tax_process'){
if($process_month === $start_month){
//Get Employee Query
$emp_sal_query = 'SELECT employee_code,role,'.$column_label.' FROM cw_employees where trans_status = 1 and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m-%d") <= "' . $end_date . '" and cw_employees.termination_status = 0 ' .$where_query.' ';
}else{
//Get Employee Query
$emp_sal_query = 'SELECT a.employee_code,a.role,'.$column_label.' FROM cw_transactions as a INNER JOIN (SELECT employee_code, date_format(MAX(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_transactions WHERE date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") ' .$where_query. ' GROUP BY employee_code) as groupedtt ON a.employee_code = groupedtt.employee_code AND a.transactions_month = groupedtt.max_date';
}
}else{
$emp_sal_query= 'select employee_code,role,'.$column_label.' from cw_transactions where cw_transactions.trans_status = 1 and cw_transactions.transactions_month = "'.$process_month.'" and cw_transactions.role != 1 ';
}
$emp_sal_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_sal_query')");
$emp_sal_rslt = $emp_sal_data->result_array();
$emp_sal_data->next_result();
$trans_array = array();
foreach ($emp_sal_rslt as $key => $value){
$trans_array[$value['employee_code']] = $value;
}
$proj_qry = 'select payroll_formula,formula_for,out_column from cw_payroll_formula where out_column IN ("fpt_gross") and trans_status = 1 order by abs(formula_order),abs(formula_mode) asc';
$proj_data = $this->db->query("CALL sp_a_run ('SELECT','$proj_qry')");
$proj_result = $proj_data->result();
$proj_data->next_result();
$payroll_formulas = array();
foreach ($proj_result as $key => $value) {
$payroll_formulas[$value->formula_for][$value->out_column] = $value->payroll_formula;
}
$other_income_entry_query = 'SELECT a.* FROM cw_other_income_entry as a INNER JOIN (SELECT emp_code, date_format(MAX(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_other_income_entry WHERE financial_setting_id = "'.$fin_set_id.'" and date_format(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") ' .$tax_where_query. ' GROUP BY emp_code) as groupedtt ON a.emp_code = groupedtt.emp_code AND a.effective_month = groupedtt.max_date';
$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();
foreach($other_income_entry_result as $arr){
$other_income_array[$arr['emp_code']] = $arr;
}
if((int)$proof_wise_val === 1){
$section_dec_query = 'SELECT * from cw_tax_proof_entry where finacial_setting_id = ' .$fin_set_id. ' and trans_status = 1 ' .$tax_where_query. '';
}else{
$process_month_dt = "01-".$process_month;
$section_dec_query = 'SELECT a.* FROM cw_declaration_entry as a INNER JOIN (SELECT emp_code, date_format(MAX(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_declaration_entry where finacial_setting_id = "'.$fin_set_id.'" and date_format(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") ' .$tax_where_query. ' GROUP BY emp_code) as groupedtt ON a.emp_code = groupedtt.emp_code AND a.effective_month = groupedtt.max_date ';
}
$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();
foreach($section_dec_result as $arr){
$section_dec_array[$arr['emp_code']] = $arr;
}
//Check Tax Calculation Exist Query
$exist_tax_qry = 'select count(*) as count_val,prime_tax_calculation_id,emp_code from '.$table_name.' where process_month = "' . $process_month . '" and trans_status = 1 group by emp_code';
$exist_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_tax_qry')");
$exist_tax_result = $exist_tax_data->result_array();
$exist_tax_data->next_result();
foreach($exist_tax_result as $arr){
$exist_tax_array[$arr['emp_code']] = $arr;
}
// 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,annual_limit 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();
//Get Earnings Query
$pf_match = "";
$earn_qry = "employee_code,transactions_month";
foreach($earnings_columns as $key => $value){
$formula = str_replace('@', '', $formula_array[$key]);
if($formula){
$earn_comp = $value . '+(' . ($formula).')';
}else{
$earn_comp = $value;
}
if($professional_tax_db){
$earn_comp = $professional_tax_db . ',' . $earn_comp;
}
if($pf_match_formula){
$pf_match = str_replace('@', '', $pf_match_formula);
$pf_match = ',' . $pf_match . ' AS pf_value';
}else{
$pf_match = "";
}
$earn_qry .= ','.$earn_comp . ' AS ' . $value;
}
if($earn_qry){
if($pf_match){
$earn_qry = $earn_qry.$pf_match;
}
}
//Get Earning Data
$earn_trans_query = 'SELECT '.$earn_qry.' FROM cw_transactions WHERE cw_transactions.trans_status = 1 and date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y"), "%Y-%m-%d") >= "'.$start_date.'" and date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y"), "%Y-%m-%d") <= "'.$process_date.'" '.$where_query;
$earn_trans_data = $this->db->query("CALL sp_a_run ('SELECT','$earn_trans_query')");
$earn_trans_result = $earn_trans_data->result_array();
$earn_trans_data->next_result();
foreach($earn_trans_result as $arr){
$earnings_result_array[$arr['transactions_month']][$arr['employee_code']] = $arr;
}
$tr_line = "";
$table_data = "";
$tax_insert_values = "";
$tax_update_values = "";
$offline_tds = 0;
$cess_value = 0;
foreach ($emp_rslt as $key => $emp){
$employee_code = $emp->employee_code;
$emp_role = $emp->role;
$metro = $emp->metro;
$emp_age = $emp->emp_age;
$tax_location = $emp->professional_tax_location;
$income_tax_type = $pro_income_tax_type ?? $emp->income_tax_type;
$emp_status = (int)$emp->termination_status;
if(!$tax_location){
$tax_location = 1;
}
$date_of_joining = $emp->date_of_joining;
if(!$income_tax_type){
$income_tax_type = 2; //Default NEW Tax Regime will be taken
}
$emp_trans_array = $trans_array[$employee_code];
if ((int)$emp_age === 0){
$emp_age = 30; //default general people
}
if((int)$income_tax_type === 1){
$sd = $financial_info[0]->old_sd;
}else{
$sd = $financial_info[0]->new_sd;
}
$pt_proj_formula = $payroll_formulas[$emp_role]['fpt_gross'];
/** Get PT Projection value END **/
/** Earning Part Start - BSK **/
$gross_total = 0;
$final_tax_pay_month = 0;
$tax_insert_key = "";
$tax_insert_value = "";
$tax_update_qry = "";
$earning_data = $this->get_earnings_data($process_month,$employee_code,$emp_role,$earnings_columns,$proj_combine,$gross_array,$actual_months,$process_date,$trans_array,$gross_other_income_result,$fin_set_id,$proof_wise_val,$section_dec_array,$professional_tax_db,$earnings_result_array,$emp_status,$income_tax_type,$other_income_array);
$remain_count = $earning_data['count'];
$pt_actual = $earning_data['pt_actual'];
$pf_actual = $earning_data['pf_actual'];
$gross_total = $earning_data['gross_total'];
//+ve
$earned_array = $earning_data['earned_array'];
$annex_pdf_check = $earning_data['annex_pdf_check'];
$tax_insert_key .= $earning_data['tax_insert_key'];
$tax_insert_value .= $earning_data['tax_insert_value'];
$tax_update_qry .= $earning_data['tax_update_qry'];
$section10_data = $this->get_section10_data($employee_code,$income_tax_type,$sec10_result,$earned_array,$proof_wise_val,$section_dec_array,$process_month,$gross_total,$hra_formula,$actual_rent,$pct_metro,$non_metro,$actual_rent_pct,$metro,$fin_set_id,$date_of_joining,$start_date,$end_date);
$gross_total = $section10_data['gross_total'];
$tax_insert_key .= $section10_data['tax_insert_key'];
$tax_insert_value .= $section10_data['tax_insert_value'];
$tax_update_qry .= $section10_data['tax_update_qry'];
$sections_data = $this->get_sections_data($eq,$emp_trans_array,$employee_code,$income_tax_type,$section_array,$section_dec_array,$pt_proj_formula, $tax_location,$remain_count,$professional_tax_db,$pt_gross_db,$earned_array,$proof_wise_val,$process_month,$fixed_pf_db,$gross_total,$pt_actual,$pf_actual,$other_income_result,$other_income_array,$emp_status,$sd);
$gross_total = $sections_data['gross_total'];
$tax_insert_key .= $sections_data['tax_insert_key'];
$tax_insert_value .= $sections_data['tax_insert_value'];
$tax_update_qry .= $sections_data['tax_update_qry'];
//Income Rounded by 10 Rupees
$gross_total = ceil($gross_total / 10) * 10; //10 Rupee Rounding
$gross_total = number_format((float) $gross_total, 2, '.', '');
$total_tax = 0;
$slap_type = '';
$final_value = 0;
if((int)$income_tax_type === 1){
$tax_calc_data = $this->old_regime($employee_code,$emp_age,$gross_total,$fin_set_id);
$rebate = $financial_info[0]->old_rebate;
$margin_relief = $financial_info[0]->old_m_relief;
}else{ // Old Tax End
$tax_calc_data = $this->new_regime($employee_code,$emp_age,$gross_total,$fin_set_id);
$rebate = $financial_info[0]->new_rebate;
$margin_relief = $financial_info[0]->new_m_relief;
} // New Tax End
if(!empty($tax_calc_data)){
$total_tax = $tax_calc_data['total_tax'];
$slap_type = $tax_calc_data['slab_type'];
$final_value = $tax_calc_data['final_value'];
}
$pan_card_no = $section_dec_array[$employee_code]['pan_card_no'];
$lendor_pan_card_no = $section_dec_array[$employee_code]['lendor_pan_card_no'];
//insert query collecting
$tax_insert_key .= ',slab_type,total_tax,total_tax_on_income,pan_card_no,lendor_pan_card_no';
$tax_insert_value .= ',"' . $slap_type . '","' . $final_value . '","' . $total_tax . '","' . $pan_card_no . '","' . $lendor_pan_card_no . '"';
//updated query collecting
$tax_update_qry .= ',slab_type = VALUES(slab_type),total_tax = VALUES(total_tax),total_tax_on_income = VALUES(total_tax_on_income),pan_card_no = VALUES(pan_card_no),lendor_pan_card_no = VALUES(lendor_pan_card_no)';
/** TAX SLAB Part END - BSK **/
//Less:87A of the Income Tax Act, 1961 rebate Tax refund calculation
$refund_sts = 0;
$rebate_amt = 0;
$surcharge_pct = 0;
$surcharge_charge = 0;
$margin_relief_amt = 0;
$sur_relief_amt = 0;
$margin_amt = 0;
if ($gross_total <= $rebate){
$refund_sts = 1;
$rebate_amt = $total_tax;
}else{
//Marginal relief
if($gross_total <= $margin_relief){
$margin_amt = $gross_total - $rebate;
//Marginal Relief
if($total_tax > $margin_amt){
$margin_relief_amt = $total_tax - $margin_amt;
$total_tax = $total_tax - $margin_relief_amt;
}
}else{
/** SURCHARGE Part START - BSK **/
$surcharge_arr = $this->surcharge_slab($employee_code,$emp_age,$gross_total, $total_tax,$income_tax_type,$fin_set_id);
if (!empty($surcharge_arr)){
$surcharge_pct = $surcharge_arr['surcharge_pct'];
$surcharge_charge = $surcharge_arr['surcharge_charge'];
$sur_relief_amt = $surcharge_arr['sur_margin_relief'];
}
}
}
/** SURCHARGE Part END - BSK **/
//education Cess Calculation
$cess_tax = 0;
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) - $sur_relief_amt;
$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, '.', '');
}
//with education cess metro_percentage
$cess_tax = $total_tax + $cess_value;
$cess_tax = number_format((float) $cess_tax, 2, '.', '');
}
$month_tds_val = "NIL";
//finding previous tax calculation values// map tds or default values is monthly tds
$date = new DateTime("01-$process_month 00:00:00");
$date->modify('-1 month');
$end_pre_month = $date->format("Y-m-d");
$prev_tax = 0;
$prev_tax_qry = 'select IFNULL((sum('.$one_time_tds_db.')),0)+IFNULL((sum('.$monthly_tds_db.')),0) as monthly_tax from `cw_transactions` where trans_status = 1 and date_format(str_to_date(concat("01-",cw_transactions.transactions_month),"%d-%m-%Y"),"%Y-%m-%d") BETWEEN "'.$start_date.'" AND "'.$end_pre_month.'" and employee_code="'.$employee_code.'"';
$prev_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$prev_tax_qry')");
$prev_tax_result = $prev_tax_data->result();
$prev_tax_data->next_result();
$monthly_tax = $prev_tax_result[0]->monthly_tax;
$offline_tds = $off_tds_result_array[$employee_code]['amount'];
$prev_company_tds = $prev_comp_tds_result_array[$employee_code]['previous_tax'];
$prev_tax = $monthly_tax + $offline_tds+$prev_company_tds;
if((int)$refund_sts === 0){
$final_tax_pay_month = $cess_tax - $prev_tax;
if($process_from === 'fandf_process_payroll'){
$month_tds_val = round($final_tax_pay_month);
}else{
$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, '.', '');
}else{
$final_tax_pay_month = 0;
$month_tds_val = 0;
}
if($month_tds_val < 0){
$month_tds_val = 0;
}
//insert query collecting
$tax_insert_key .= ',rebate_amt,surcharge_pct,surcharge_amt,margin_relief,tax_amt,tax_deducted,final_tax_pay,final_tax_amt,net_tax';
$tax_insert_value .= ',"' . $rebate_amt . '","' . $surcharge_pct . '"' . ',"' . $surcharge_charge . '","'.$margin_relief_amt.'","' . $cess_tax . '","' . $prev_tax . '","' . $final_tax_pay_month . '","'.$month_tds_val.'","'.$total_tax.'"';
//updated query collecting
$tax_update_qry .= ',rebate_amt = VALUES(rebate_amt),surcharge_pct = VALUES(surcharge_pct),surcharge_amt = VALUES(surcharge_amt),margin_relief = VALUES(margin_relief),tax_amt = VALUES(tax_amt),tax_deducted = VALUES(tax_deducted),final_tax_pay = VALUES(final_tax_pay),final_tax_amt = VALUES(final_tax_amt),net_tax = VALUES(net_tax)';
//Total Tax To Be Paid This Month
$tax_calculation_id = $exist_tax_array[$employee_code]['prime_tax_calculation_id'];
$created_on = date('Y-m-d H:i:s');
if((int)$tax_calculation_id > 0){
$tax_update_values .= '("' . $tax_calculation_id . '",' . $tax_insert_value . ',"' . $this->logged_id . '","' . $created_on . '","' . $cess_value . '","' . $income_tax_type . '"),';
}else{
$tax_insert_values .= '('.$tax_insert_value . ',"' . $this->logged_id . '","' . $created_on . '","' . $cess_value . '","' . $income_tax_type . '"),';
}
if($process_from !== 'projection_tax_process'){
//DON'T DELETE
$mi_qry = "cw_monthly_input";
$monthly_input_upd_qry = 'UPDATE '.$mi_qry.' SET '.$monthly_tds_db.' = "' . $month_tds_val . '" WHERE employee_code = "' . $employee_code . '" and process_month = "' . $process_month . '" and trans_status = 1';
$this->db->query("CALL sp_a_run ('UPDATE','$monthly_input_upd_qry')");
}
$tr_line .= "<tr><td>$process_month</td><td>$employee_code</td><td>$cess_tax</td><td>$prev_tax</td><td>$final_tax_pay_month</td><td>$month_count_rslt</td><td>$month_tds_val</td><td><button type = 'button' class='btn btn-info btn-sm' style='display: inline-block;' onclick = view_tax('".$employee_code."','".$process_month."')><span class='fa fa-file'> </span> View</button></td></tr>";
}
}
$tax_update_values = rtrim($tax_update_values,",");
$tax_insert_values = rtrim($tax_insert_values,",");
if($tax_update_values){
$tax_bulk_upd_qry = 'INSERT INTO '.$table_name.'(prime_tax_calculation_id,' . $tax_insert_key . ',trans_created_by,trans_created_date,edu_cess,income_tax_type) VALUES '.$tax_update_values.' ON DUPLICATE KEY UPDATE '.$tax_update_qry.',trans_updated_by = VALUES(trans_created_by),trans_updated_date = VALUES(trans_created_date),edu_cess = VALUES(edu_cess),income_tax_type = VALUES(income_tax_type)';
$bulk_upd_info = $this->db->query($tax_bulk_upd_qry);
}
if($tax_insert_values){
$tax_bulk_upd_qry = 'INSERT INTO '.$table_name.'(' . $tax_insert_key . ',trans_created_by,trans_created_date,edu_cess,income_tax_type) VALUES '.$tax_insert_values;
$bulk_upd_info = $this->db->query($tax_bulk_upd_qry);
}
$table_data = "<table class='table table-striped table-hover' id='table_info'>
<thead>
<th>Process Month</th><th>Employee Code</th><th>Overall Tax</th><th>Already Paid</th><th>Remaining Payable Amt</th><th>Remaining Months</th><th>Monthly Tds</th><th>View</th>
</thead>
<tbody>$tr_line</tbody>
</table>";
if($bulk_upd_info){
if($process_from === 'payroll_process' || $process_from === "fandf_process_payroll"){
return true;
}else{
echo json_encode(array('success' => TRUE,"message"=>"tax processed",'table_data' => $table_data));
}
}else{
echo json_encode(array('success' => FALSE,'message' => "Please Try after sometime..."));
exit(0);
}
}
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){
$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 get_earnings_data($process_month,$employee_code,$emp_role,$earnings_columns,$proj_combine,$gross_array,$actual_months,$process_date,$trans_array,$gross_other_income_result,$fin_set_id,$proof_wise_val,$section_dec_array,$professional_tax_db,$earnings_result_array,$emp_status,$income_tax_type,$other_income_array){
$tax_insert_key = "fin_set_id,emp_role,emp_code,process_month";
$tax_insert_value = '"'.$fin_set_id.'","'.$emp_role.'","'.$employee_code.'","'.$process_month.'"';
//updated query collecting
$tax_update_qry = 'emp_code = VALUES(emp_code),process_month = VALUES(process_month)';
$earned_array = array();
$proj_earn_pdf = array();
$act_earn_pdf = array();
$i = 0;
$gross_1 = 0;
$gross_2 = 0;
$gross_3 = 0;
$total_earn = 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];
$earn_column = "earn_$value";
$proj_column = "proj_$value";
if($projection_column && $emp_status === 0){
$proj = $trans_array[$employee_code][$projection_column];
}else{
$proj = 0;
}
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
$proj_earn_pdf[$value] = $proj;
$act_val = $earnings_result_array[$month][$employee_code][$value];
$earned_val = $earnings_result_array[$month][$employee_code][$value];
$pt_value = $earnings_result_array[$month][$employee_code][$professional_tax_db];
$pf_value = $earnings_result_array[$month][$employee_code]['pf_value'];
$act_earn_pdf[$value][$month] = $act_val;
$total_actual = $total_actual + $earned_val; //newly inserted for tax 21FEB2020
//Professional Tax Actual
if((int)$income_tax_type === 1){
$pt_actual = $pt_actual + $pt_value;
$pf_actual = $pf_actual + $pf_value;
//$pf_proj = $pf_value;
}
}else{ //For Projection
$count++;
}
}
if($emp_status === 1){ //If Inactive Employee
$total_proj = 0;
}else{
$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;
} else if ($gross_section === "2") {
$gross_2 = $gross_2 + $total;
} else if ($gross_section === "3"){
$gross_3 = $gross_3 + $total;
}
$i++;
$earned_array[$value] = $total;
//insert query collecting
$tax_insert_key .= ','.$earn_column.','. $proj_column;
$tax_insert_value .= ',"' . $total_actual . '","' . $total_proj . '"';
//updated query collecting
$tax_update_qry .= ','.$earn_column . ' = VALUES('.$earn_column . '),' . $proj_column . ' = VALUES('.$proj_column . ')';
}
$other_income_val = 0;
foreach($gross_other_income_result as $gross_key => $gross_value){
$income_type = $gross_value->income_type;
$other_income_column = $gross_value->other_income_column;
$annual_limit = $gross_value->annual_limit;
if((int)$proof_wise_val === 1){
$other_income_val = $section_dec_array[$employee_code][$other_income_column];
}else{
$other_income_val = $other_income_array[$employee_code][$other_income_column];
if($annual_limit > 0){
$other_income_val = min($other_income_val, $annual_limit);
}
}
//and cw_other_income.income_type in (3,4,5)
if((int)$income_type === 3){
$gross_1 = $gross_1 + $other_income_val;
}else
if((int)$income_type === 4){
$gross_2 = $gross_2 + $other_income_val;
}else
if((int)$income_type === 5){
$gross_3 = $gross_3 + $other_income_val;
}
if($other_income_val > 0){
$total_earn = $total_earn + $other_income_val;
}
//insert query collecting
$tax_insert_key .= ','.$other_income_column;
$tax_insert_value .= ',"' . $other_income_val . '"';
//updated query collecting
$tax_update_qry .= ','.$other_income_column . ' = VALUES('.$other_income_column . ')';
}
$gross_total = number_format((float)$total_earn, 2, '.', '');
$proj_count_pdf = array();
foreach ($actual_months as $actual){
$month = $actual->format("m-Y");
foreach ($act_earn_pdf as $act_pdf => $act_val) {
foreach ($proj_earn_pdf as $proj_pdf => $proj_val) {
if (!array_key_exists($month, $act_val)) {
$total_array[$month] = $proj_val;
$proj_count_pdf[$proj_pdf][$month] = $proj_val;
}
}
}
}
$annex_pdf_check = array_merge_recursive($act_earn_pdf,$proj_count_pdf);
//insert query collecting
$tax_insert_key .= ",earning_total,gross_17a,gross_17b,gross_17c";
$tax_insert_value .= ',"' . $gross_total . '","' . $gross_1 . '","' . $gross_2 . '","' . $gross_3 . '"';
$tax_update_qry .= ',earning_total = VALUES(earning_total),gross_17a = VALUES(gross_17a),gross_17b = VALUES(gross_17b),gross_17c = VALUES(gross_17c)';
/** Earning Part END - BSK **/
return array("earned_array"=>$earned_array,"gross_total"=>$gross_total,"tax_insert_key"=>$tax_insert_key,"tax_insert_value"=>$tax_insert_value,"tax_update_qry"=>$tax_update_qry,"count"=>$count,"pt_actual"=>$pt_actual,"pf_actual"=>$pf_actual,"annex_pdf_check"=>$annex_pdf_check);
}
public function get_section10_data($employee_code,$income_tax_type,$sec10_result,$earned_array,$proof_wise_val,$section_dec_array,$process_month,$gross_total,$hra_formula,$actual_rent,$pct_metro,$non_metro,$actual_rent_pct,$metro,$fin_set_id,$date_of_joining,$start_date,$end_date){
$dec_value = 0;
$rent = 0;
$actual_rent = 0;
$metro_rslt = 0;
$total_sec10 = 0;
$sec10_least_value = 0;
if((int)$metro === 1){
$metro_pct = $pct_metro;
}else{
$metro_pct = $non_metro;
}
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_limit = $value->tax_subsection_limit;
$bill_required = $value->bill_required;
//Check Data for get Whichever is Least
$earned_value = $earned_array[$earnings];
$dec_value = $section_dec_array[$employee_code][$tax_subsection_column];
if (!$dec_value) {
$dec_value = "0.00";
}
$house_rent = "0.00";
$rent = "0.00";
$actual_rent = "0.00";
//HRA CALCUALTIONS FOR ALL
if($tax_subsection_column === 'taxsubsec_1'){
if((int)$income_tax_type === 1){
$earned_hra = $earned_array[$earnings];
$house_rent = $section_dec_array[$employee_code]["tax_house_rent"];
if(($date_of_joining > $start_date) && ($date_of_joining <= $end_date)){
//Get No of Days for HRA If Employee joined inbetween Fin Year
$datediff = strtotime($date_of_joining) - strtotime($start_date);
$nonworked_days = round($datediff / (60 * 60 * 24));
$worked_days = 365 - $nonworked_days;
$house_rent = round(($house_rent/365)*$worked_days);
}
//Get declaration House Rent entry Value
if($house_rent === "0.00" || !$house_rent && $proof_wise_val === 0){
$process_month_dt = "01-".$process_month;
$hr_query = 'SELECT sum(a.metro_rent_paid)+sum(a.non_metro_rent_paid) as house_rent FROM cw_house_rent_entry as a INNER JOIN (SELECT employee_code, date_format(MAX(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_house_rent_entry where employee_code = "' . $employee_code . '" and financial_setting_id = "'.$fin_set_id.'" and date_format(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") GROUP BY employee_code) as groupedtt ON a.employee_code = groupedtt.employee_code AND a.effective_month = groupedtt.max_date';
$hr_data = $this->db->query("CALL sp_a_run ('SELECT','$hr_query')");
$hr_result = $hr_data->result();
$hr_data->next_result();
$house_rent = $hr_result[0]->house_rent;
}
// $rent_received = $house_rent;
$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(is_numeric($hra_formula)){
$hra_res = $hra_formula;
}else{
$hra_res = $eq->solveIF($hra_formula);
}
$metro_rslt = round($hra_formula * ($metro_pct / 100));
if($house_rent){
$actual_rent = round($hra_res * ($actual_rent_pct / 100));
$rent = $house_rent - $actual_rent;
if ($rent > 0) {
$sec10_least_value = min($earned_hra, $metro_rslt, $rent);
}else{
$rent = 0;
}
}
}
//insert query collecting
$tax_insert_key .= ",house_rent_paid,rent,actual_rent_pct,actual_rent,metro_rslt,metro_pct";
$tax_insert_value .= ',"' . $house_rent . '"'.',"' . $rent . '"'.',"' . $actual_rent_pct . '"'.',"' . $actual_rent . '"'.',"' . $metro_rslt . '"'.',"' . $metro_pct . '"';
$tax_update_qry .= ',house_rent_paid = VALUES(house_rent_paid)'.',rent = VALUES(rent)'.',actual_rent_pct = VALUES(actual_rent_pct)'.',actual_rent = VALUES(actual_rent),metro_rslt = VALUES(metro_rslt),metro_pct = VALUES(metro_pct)';
//insert query collecting
$tax_insert_key .= ",$tax_subsection_column";
$tax_insert_value .= ',"'.round($sec10_least_value).'"';
$tax_update_qry .= ','.$tax_subsection_column.' = VALUES('.$tax_subsection_column.')';
}else{
if((int)$bill_required === 1){
$sec10_least_value = min($earned_value, $tax_subsection_limit, $dec_value);
}else{
$dec_value = number_format((float) $earned_value, 2, '.', '');
$sec10_least_value = min($dec_value, $tax_subsection_limit); //,$dec_value
}
/*if((int)$income_tax_type === 2){
$sec10_least_value = 0;
}*/
$tax_insert_key .= ',' . $tax_subsection_column;
$tax_insert_value .= ',"' . $sec10_least_value . '"';
$tax_update_qry .= ',' . $tax_subsection_column . ' = VALUES('.$tax_subsection_column.')';
}
$total_sec10 = $total_sec10 + $sec10_least_value;
$total_sec10 = number_format((float) $total_sec10, 2, '.', '');
}
//insert query collecting
$tax_insert_key .= ',tot_' . $tax_section_column;
$tax_insert_value .= ',"' . $total_sec10 . '"';
$tax_update_qry .= ',tot_' . $tax_section_column . ' = VALUES(tot_' . $tax_section_column . ')';
$gross_total = $gross_total - $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 = VALUES(gross_tot_taxsec_1)';
/** SECTION 10 Part END - BSK **/
return array("gross_total"=>$gross_total,"tax_insert_key"=>$tax_insert_key,"tax_insert_value"=>$tax_insert_value,"tax_update_qry"=>$tax_update_qry);
}
public function get_sections_data($eq,$emp_trans_array,$employee_code,$income_tax_type,$section_array,$section_dec_array,$pt_proj_formula, $tax_location,$count,$professional_tax_db,$pt_gross_db,$earned_array,$proof_wise_val,$process_month,$fixed_pf_db,$gross_total,$pt_actual,$pf_actual,$other_income_result,$other_income_array,$emp_status,$sd){
/** SUB SECTIONS Part START - BSK **/
$sub_section_line = "";
foreach ($section_array as $key => $sub_section){
$tot_limit = 0;
$tot_deduct = 0;
$total_sub = 0;
$sub_section_line .= "<tr><td style='font-weight: bold;font-size: 16px;'>$key</td><td style='font-size: 12px;'>Declare</td><td style='font-size: 12px;'>Limit</td><td></td></tr>";
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_limit = $sub_value['tax_subsection_limit'];
$tax_section = $sub_value['tax_section'];
$bill_required = $sub_value['bill_required'];
$section_limit = $sub_value['section_limit'];
$dec_value = $section_dec_array[$employee_code][$tax_subsection_column];
if($tax_subsection_limit === '0.00'){
$sub_least_value = $dec_value;
}else{
$sub_least_value = min($dec_value, $tax_subsection_limit);
}
if ($tax_subsection_column === "taxsubsec_8"){
$sub_least_value = $sd;
$total_sub = $total_sub + $sub_least_value;
} else if ($tax_subsection_column === "taxsubsec_10") {
$pt_proj = 0;
$pt_total = 0;
if((int)$income_tax_type === 1){
/** Get PT Projection value START **/
if($emp_status === 0){
$pt_proj = $this->get_pt_projection($eq,$pt_proj_formula, $tax_location, $process_month, $count, $employee_code, $professional_tax_db,$pt_gross_db,$emp_trans_array);
}else{
$pt_proj = 0;
}
$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, '.', '');
$sub_least_value = $pt_total;
}
//insert query collecting
$tax_insert_key .= ',pt_proj' .',pt_actual' .',pt_total' ;
$tax_insert_value .= ',"' . $pt_proj . '"' . ',"' . $pt_actual . '"' . ',"' . $pt_total . '"';
$tax_update_qry .= ',pt_proj = VALUES(pt_proj),pt_actual = VALUES(pt_actual),pt_total = VALUES(pt_total)';
}else
if ($tax_subsection_column === "taxsubsec_11") {
/** Get PF Projection value START **/
$pf_elig = $emp_trans_array['pf_eligibility']; // Check PF Eligibility
if((int)$pf_elig === 1 and (int)$income_tax_type === 1){
$fixed_pf = $emp_trans_array[$fixed_pf_db];
$pf_proj = $fixed_pf*$count;
if($emp_status === 1){
$pf_proj = 0;
}
/** 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, '.', '');
$sub_least_value = $pf_total;
}else{
$pf_proj = 0;
$pf_actual = 0;
$pf_total = 0;
$match_value = 0;
$sub_least_value = 0;
}
//insert query collecting
$tax_insert_key .= ',pf_proj' . ',pf_actual' . ',pf_total' . ',section_matching' ;
$tax_insert_value .= ',"' . $pf_proj . '"' . ',"' . $pf_actual . '"' . ',"' . $pf_total . '"' . ',"' . $match_value . '"';
$tax_update_qry .= ',pf_proj = VALUES(pf_proj),pf_actual = VALUES(pf_actual),pf_total = VALUES(pf_total),section_matching = VALUES(section_matching)';
}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@";
//Get Value from Transaction
$trans_query = 'SELECT ' . $for_rslt . ' FROM cw_transactions WHERE cw_transactions.trans_status = 1 and transactions_month = "' . $process_month . '" and employee_code = "' . $employee_code . '"';
$trans_data = $this->db->query("CALL sp_a_run ('SELECT','$trans_query')");
$trans_result = $trans_data->result();
$trans_data->next_result();
$for_value = $trans_result[0]->$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((int)$income_tax_type === 2){
$sub_least_value = 0;
} */
} else {
$total_sub = $total_sub + $sub_least_value;
$tot_limit = $tot_limit + $tax_subsection_limit;
$tot_deduct = $tot_deduct + $dec_value;
}
}
if ($tax_section_column === "taxsec_3") {
$total_sub = min($tot_deduct, $section_limit);
$total_sub = number_format((float) $total_sub, 2, '.', '');
}
/* if((int)$income_tax_type === 2){
$dec_value = 0;
$total_sub = 0;
}*/
//insert query collecting
$tax_insert_key .= ',' . $tax_subsection_column;
$tax_insert_value .= ',"' . $sub_least_value . '"';
$tax_update_qry .= ',' . $tax_subsection_column . ' = VALUES(' . $tax_subsection_column . ')';
}
//insert query collecting
$tax_insert_key .= ',tot_' . $tax_section_column;
$tax_insert_value .= ',"' . $total_sub . '"';
$tax_update_qry .= ',tot_' . $tax_section_column . ' = VALUES(tot_' . $tax_section_column . ')';
$gross_total = $gross_total - $total_sub;
$gross_total = number_format((float) $gross_total, 2, '.', '');
//insert query collecting
$tax_insert_key .= ',gross_tot_' . $tax_section_column;
$tax_insert_value .= ',"' . $gross_total . '"';
$tax_update_qry .= ',gross_tot_' . $tax_section_column . ' = VALUES(gross_tot_' . $tax_section_column . ')';
if($tax_section_column === "taxsec_2"){
$other_income_data = $this->get_other_income_data($employee_code,$income_tax_type,$other_income_result,$other_income_array,$gross_total,$earned_array,$section_dec_array,$process_month,$proof_wise_val);
$gross_total = $other_income_data['gross_total'];
$tax_insert_key .= $other_income_data['tax_insert_key'];
$tax_insert_value .= $other_income_data['tax_insert_value'];
$tax_update_qry .= $other_income_data['tax_update_qry'];
}
}
/** SUB SECTIONS Part END - BSK **/
return array("gross_total"=>$gross_total,"tax_insert_key"=>$tax_insert_key,"tax_insert_value"=>$tax_insert_value,"tax_update_qry"=>$tax_update_qry);
}
public function get_pt_projection($eq,$pt_proj_formula, $tax_location, $process_month, $count, $employee_code, $professional_tax_db,$pt_gross_db,$emp_trans_array){
$pt_proj_amt = 0;
if ($pt_proj_formula) {
$preg_match = preg_match_all('#\@(.*?)\@#', $pt_proj_formula, $match);
foreach ($match[1] as $for_rslt){
$find_value = "@$for_rslt@";
$for_value = $emp_trans_array[$for_rslt];
$pt_proj_formula = str_replace($find_value, $for_value, $pt_proj_formula);
}
if ($pt_proj_formula) {
$pt_proj_amt = $eq->solveIF($pt_proj_formula);
}
}
$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 <= "' . $pt_proj_amt . '" and earning_range_to >= ' . $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 = 6;
$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('.$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(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 <= "' . $total_earn . '" and earning_range_to >= ' . $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(concat("01-",transactions_month), "%d-%m-%Y") , "%Y-%m-%d") BETWEEN date_format("' . $last_month_date . '", "%Y-%m-%d") and date_format("' . $process_month_date . '", "%Y-%m-%d")';
$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)) {
$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;
$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 = 6;
$tot_count = 0;
//calculating pending projection count loop
$first = false;
if (in_array($process_month_val, $first_period)) {
$first = true;
$cur_count = array_search($process_month_val, array_values($first_period));
$tot_count = (int) $loop_count - (int) $cur_count;
}elseif(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_amt*(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"));
if ($last_month_date === $process_month_date){
$previous_tax_qry = 'select ifnull(sum(ept_gross),0) as total_earnings from cw_transactions where trans_status =1 and employee_code = "' . $employee_code . '" and transactions_month = date_format("' . $process_month_date . '", "%m-%Y")';
}else{
$previous_tax_qry = 'select ifnull(sum(ept_gross),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 <= "' . $total_earn . '" and earning_range_to >= ' . $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();
$proj_tax = $find_tax_result[0]->ptax_amt;
$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;
if($first){
$prof_tax = ($proj_tax*2) - $last_tax_val;
}else{
$prof_tax = $proj_tax;
}
//}
}
return round($prof_tax);
}
public function get_other_income_data($employee_code,$income_tax_type,$other_income_result,$other_income_array,$gross_total,$earned_array,$section_dec_array,$process_month,$proof_wise_val){
$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;
$other_income_column = $income_value->other_income_column;
$annual_limit = $income_value->annual_limit;
if((int)$proof_wise_val === 1){
$income_val = $section_dec_array[$employee_code][$other_income_column];
}else{
$income_val = $other_income_array[$employee_code][$other_income_column];
if($annual_limit > 0){
$income_val = min($income_val, $annual_limit);
}
}
if(!$income_val){
$income_val = 0.00;
}
if ((int) $income_type_id === 1) {
$tot_add = $tot_add + $income_val;
} else {
$tot_less = $tot_less + $income_val;
}
$tax_insert_key .= ',' . $other_income_column;
$tax_insert_value .= ',"' . $income_val . '"';
$tax_update_qry .= ',' . $other_income_column . ' = VALUES(' . $other_income_column . ')';
}
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, '.', '');
//insert query collecting
$tax_insert_key .= ',tot_other';
$tax_insert_value .= ',"' . $gross_total . '"';
//updated query collecting
$tax_update_qry .= ',tot_other = VALUES(tot_other)';
$final_value = number_format((float) $gross_total, 2, '.', '');
//insert query collecting
$tax_insert_key .= ',final_gross';
$tax_insert_value .= ',"' . $final_value . '"';
//updated query collecting
$tax_update_qry .= ',final_gross = VALUES(final_gross)';
return array("gross_total"=>$gross_total,"tax_insert_key"=>$tax_insert_key,"tax_insert_value"=>$tax_insert_value,"tax_update_qry"=>$tax_update_qry);
}
public function old_regime($employee_code,$emp_age,$gross_total,$fin_set_id){
$final_value = $gross_total;
/** TAX SLAB Part START - BSK **/
//Get TAX Slab
$tax_slab_query = 'SELECT 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_income_tax_slab on cw_income_tax_slab.prime_income_tax_slab_id = cw_income_tax_slab_income_tax_slab_details.prime_income_tax_slab_id inner join cw_slap_type on cw_slap_type.prime_slap_type_id = cw_income_tax_slab.slap_type WHERE min_age <= "' . $emp_age . '" and max_age >= "' . $emp_age . '" and financial_setting_id = "' . $fin_set_id . '" and 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();
$total_tax = 0;
$tax_value = 0;
foreach ($tax_slab_result as $tax => $tax_slab) {
$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;
//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;*/
$tax_range = ($tax_range_end - $tax_range_start)+1;
$tax_value = ($tax_range * $tax_amount) / 100;
$final_tax = $final_tax - $tax_range;
} 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, '.', '');
}
return array("final_value"=>$final_value,"slab_type"=>$slap_type,"total_tax"=>$total_tax);
}
public function new_regime($employee_code,$emp_age,$gross_total,$fin_set_id){
/** TAX SLAB Part START - BSK **/
//Get New TAX Slab
$final_value = $gross_total;
$tax_slab_query = 'SELECT tax_range_start,tax_range_end,tax_amount_percentage as tax_amount,cw_slap_type.slap_type,cw_slap_type.prime_slap_type_id FROM `cw_income_tax_slab_new_income_tax_slab_details_new` inner join cw_income_tax_slab_new on cw_income_tax_slab_new.prime_income_tax_slab_new_id = cw_income_tax_slab_new_income_tax_slab_details_new.prime_income_tax_slab_new_id inner join cw_slap_type on cw_slap_type.prime_slap_type_id = cw_income_tax_slab_new.slab_type WHERE min_age <= "' . $emp_age . '" and max_age >= "' . $emp_age . '" and financial_setting_id = "' . $fin_set_id . '" and cw_income_tax_slab_new_income_tax_slab_details_new.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();
$total_tax = 0;
$tax_value = 0;
foreach ($tax_slab_result as $tax => $tax_slab) {
$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;
//Check Value Inside the range
if (((int)$tax_range_start === 0 || (int)$tax_range_start === 1) && (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_range = ($tax_range_end - $tax_range_start)+1;
$tax_value = ($tax_range * $tax_amount) / 100;
$final_tax = $final_tax - $tax_range;
} 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, '.', '');
}
return array("final_value"=>$final_value,"slab_type"=>$slap_type,"total_tax"=>$total_tax);
}
public function surcharge_slab($employee_code,$emp_age,$earn_value, $total_tax,$income_tax_type,$fin_set_id){
$surcharge_slab_qry = 'select * from cw_surcharge_slab where income_start_amount <="' . $earn_value . '" and income_end_amount >="' . $earn_value . '"';
$surcharge_slab_data = $this->db->query("CALL sp_a_run ('SELECT','$surcharge_slab_qry')");
$surcharge_slab_result = $surcharge_slab_data->result();
$surcharge_slab_data->next_result();
$surcharge_slab_arr = array();
if($surcharge_slab_result){
$surcharge_pct = $surcharge_slab_result[0]->surcharge_percentage;
$sur_relief_amt = $surcharge_slab_result[0]->margin_relief;
if((int)$surcharge_pct !== 0){
$slab_start_amt = $surcharge_slab_result[0]->income_start_amount;
$slab_start_amt_pct = $slab_start_amt - 1;
if((int)$income_tax_type === 1){
$tax_calc_data = $this->old_regime($employee_code,$emp_age,$slab_start_amt_pct,$fin_set_id);
}else{
$tax_calc_data = $this->new_regime($employee_code,$emp_age,$slab_start_amt_pct,$fin_set_id);
}
if(!empty($tax_calc_data)){ //normal tax for surcharge value(slab_start_amt_pct)
$normal_tax = $tax_calc_data['total_tax'];
}
$inc_surcharge_tax = round($total_tax * ($surcharge_pct / 100));
$total_tax = $inc_surcharge_tax + $total_tax; //inc surcharge tax
$income_diff = $earn_value - $slab_start_amt_pct;
$tax_diff = $total_tax - $normal_tax;
if($tax_diff > $income_diff){
$margin_relief = $tax_diff - $income_diff;
//$inc_surcharge_tax = $inc_surcharge_tax - $margin_relief; //final surcharge
}else{
$margin_relief = 0;
//$inc_surcharge_tax = 0;
}
}else{
$margin_relief = 0;
$inc_surcharge_tax = 0;
}
$surcharge_slab_arr = array(
'surcharge_pct' => $surcharge_pct,
'surcharge_charge' => $inc_surcharge_tax,
'sur_margin_relief' => $margin_relief
);
}
return $surcharge_slab_arr;
}
// Get Default Financial Year
public function get_financial_year(){
$fin_query = 'SELECT prime_financial_setting_id,start_date,end_date,old_rebate,old_m_relief,new_rebate,new_m_relief,old_sd,new_sd,tan_no,pan_or_gnr_no 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();
return $fin_result;
}
}
?>