File: /home/cafsindia/hrms_patroniss_com/application/controllers/Income_tax_process.php
<?php
if (!defined('BASEPATH'))
exit('No direct script is allowed');
require_once("Base_controller.php");
class Income_tax_process extends Base_controller
{
public function __construct()
{
parent::__construct('income_tax_process');
if (!$this->Appconfig->isAppvalid()) {
redirect('config');
}
$this->collect_base_info();
// Load pdf library
$this->load->library('pdf');
}
// LOAD PAGE WITH TABLE DATA
public function index()
{
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1')");
$role_result = $role_info->result();
$role_info->next_result();
$process_role[""] = "---- Select Role ----";
foreach ($role_result as $for) {
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$process_role[$role_id] = $category_name;
}
$data['process_role'] = $process_role;
$data['table_headers'] = $this->xss_clean(get_dbtable_headers($this->table_info));
$data['fliter_list'] = $this->fliter_list;
$data['link_info'] = $this->quick_link;
$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);
}
//SAVE MODEL DATA TO DATA BASE
public function process_tax(){
require_once "./dist/eos-1.0.0/eos.class.php";
$eq = new eqEOS();
$process_month = $this->input->post('process_month');
$annexure_sts = $this->input->post('annexure_sts');
$process_type = (int) $this->input->post('process_type');
$process_emp_code = $this->input->post('process_emp_id');
$process_role = (int) $this->input->post('process_role');
$process_mode = $this->input->post('process_mode');
$proof_wise = $this->input->post('proof_wise');
$month_name = date("F", strtotime("01-" . $process_month));
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
//echo "BSK $process_month :: $process_emp_code"; die;
//Process Tax mode =1 and mode 2 delete the tax only delete call
$del_query = "";
if ((int) $process_mode === 2) {
if ($process_type === 1) {
$del_query = 'emp_code = "' . $process_emp_code . '"';
} else if ($process_type === 2) {
$del_query = 'emp_role = "' . $process_role . '"';
}
$update_tax_qry = 'UPDATE cw_tax_calculation SET trans_updated_by = "' . $logged_id . '",trans_updated_date = "' . $today_date . '" , trans_status = 0 WHERE ' . $del_query . ' and process_month = "' . $process_month . '"';
$update_info = $this->db->query("CALL sp_a_run ('UPDATE','$update_tax_qry')");
$update_info->next_result();
echo json_encode(array(
'success' => False,
'msg' => "Data deleted successfully!"
));
exit(0);
} else {
$proof_wise_val = 0;
if ($proof_wise === "on") {
$proof_wise_val = 1;
}
$annexure_sts_val = 0;
if ($annexure_sts === "on") {
$annexure_sts_val = 1;
}
$reprocess_val = 0;
//$process_month = $process_month[0]."-".$process_month[1];
$where_query = "";
$cond_query = "";
if ($process_type === 1) {
$where_query = ' and employee_code = "' . $process_emp_code . '" ';
$cond_query = ' and cw_employees.employee_code = "' . $process_emp_code . '" ';
} else if ($process_type === 2) {
$where_query = ' and role = "' . $process_role . '"';
$cond_query = ' and cw_employees.role = "' . $process_role . '"';
}
$payroll_exist_qry = 'select count(*) payroll_count from cw_transactions where trans_status = 1 and transactions_month ="' . $process_month . '" ' . $where_query;
$payroll_exist_info = $this->db->query("CALL sp_a_run ('RUN','$payroll_exist_qry')");
$payroll_exist_result = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$payroll_count = $payroll_exist_result[0]->payroll_count;
if ((int) $payroll_count === 0) {
echo json_encode(array(
'success' => false,
'msg' => "Payroll Not Generated..?"
));
exit(0);
}
//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 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();
if (!$sec10_result) {
echo json_encode(array(
'success' => False,
'msg' => "Income Matching Should not Empty!"
));
exit(0);
}
$fin_set_id = $this->financial_info[0]->prime_financial_setting_id;
$start_date = $this->financial_info[0]->start_date;
$end_date = $this->financial_info[0]->end_date;
//echo "BSK $start_date :: $end_date"; die;
//For Display in PDF
$start_text = date("d-M-Y", strtotime($start_date));
$end_text = date("d-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);
$proj_month = date('m-Y', strtotime('+1 month', strtotime($process_date)));
//company details
$company_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT company_name,address,city,state,country FROM `cw_company_information` where cw_company_information.trans_status = 1')");
$company_result = $company_info->result();
$company_info->next_result();
$company_name = $company_result[0]->company_name;
$company_address = $company_result[0]->address;
$company_city = $company_result[0]->city;
$company_state = $company_result[0]->state;
$company_country = $company_result[0]->country;
//Dynamic column list array
$get_column_qry = 'select * from cw_payroll_function_map where map_statutory_name= 3 and 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();
$loc_column_list = array(
"earned_gross" => "Earned Gross",
"paid_days" => "Paid Days",
"month_days" => "Month Days",
"fixed_basic" => "Fixed Basic",
"fixed_gross" => "Fixed Gross",
"professional_tax_amount" => "Professional Tax",
"esi_loc" => "ESI Location",
"esi_elig" => "ESI Eligibility"
);
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;
}
}
$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;
$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;
if ((int) $manual_entry === 1) { //manual entry yes for no need system calculated for pt projections.
$pt_projection_formula = $matching_components;
}
//echo $pt_projection_formula; die;
//Get Employee Query
$emp_info_query = 'select cw_employees.prime_employees_id,role,metro,employee_code,emp_age,emp_name,cw_designation.designation,professional_tax_location FROM cw_employees INNER JOIN cw_designation on cw_designation.prime_designation_id = cw_employees.designation WHERE cw_employees.trans_status = 1 and cw_employees.termination_status = 0 and cw_employees.role != 1' . $cond_query . 'order by cw_employees.employee_code limit 0,100';
//echo $emp_info_query; die;
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_info_query')");
$emp_rslt = $emp_data->result();
$emp_data->next_result();
//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 cw_income_type.income_type,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 = 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();
//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();
$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 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();
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
);
}
//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);
$column_qry = 'SELECT GROUP_CONCAT(label_name) AS columns FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND input_view_type IN (1,2) and transaction_type = 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;
$emp_sal_query = 'select employee_code,role,' . $column_label . ' from cw_employees where cw_employees.trans_status = 1 and cw_employees.termination_status = 0 and cw_employees.role != 1 ' . $cond_query;
$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();
$emp_array = array_reduce($emp_sal_rslt, function($result, $arr)
{
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
$trans_array = $this->formula($emp_array);
$formula_qry = 'select payroll_formula,formula_for from cw_payroll_formula where out_column = "pt_projection" and trans_status = 1 order by abs(formula_order),abs(formula_mode) asc';
$formula_data = $this->db->query("CALL sp_a_run ('SELECT','$formula_qry')");
$formula_result = $formula_data->result();
$formula_data->next_result();
$formula_result = array_map(function($rslt){
$return_data['formula'] = $rslt;
$return_data['formula_for'] = $rslt->formula_for;
return $return_data;
}, $formula_result);
$formula_result = array_column($formula_result,'formula','formula_for');
$pf_proj_qry = 'select payroll_formula,formula_for from cw_payroll_formula where out_column = "pf_projection" and trans_status = 1 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_result = array_map(function($rslt){
$return_data['formula'] = $rslt;
$return_data['formula_for'] = $rslt->formula_for;
return $return_data;
}, $pf_proj_result);
$pf_proj_result = array_column($pf_proj_result,'formula','formula_for');
/*$formula_qry = 'select payroll_formula,formula_for from cw_payroll_formula where out_column = "pt_projection" and trans_status = 1 order by abs(formula_order),abs(formula_mode) asc';
$formula_data = $this->db->query("CALL sp_a_run ('SELECT','$formula_qry')");
$formula_result = $formula_data->result();
$formula_data->next_result();
$formula_result = array_map(function($rslt){
$return_data['formula'] = $rslt;
$return_data['formula_for'] = $rslt->formula_for;
return $return_data;
}, $formula_result);
$formula_result = array_column($formula_result,'formula','formula_for');
$pf_proj_qry = 'select payroll_formula,formula_for from cw_payroll_formula where out_column = "pf_projection" and trans_status = 1 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_result = array_map(function($rslt){
$return_data['formula'] = $rslt;
$return_data['formula_for'] = $rslt->formula_for;
return $return_data;
}, $pf_proj_result);
$pf_proj_result = array_column($pf_proj_result,'formula','formula_for');*/
$final_line = "";
$tr_line = "";
$sec10_upd_query = "";
$table_data = "";
$pdf_data = array();
foreach ($emp_rslt as $key => $emp) {
$employee_code = $emp->employee_code;
$emp_name = $emp->emp_name;
$emp_role = $emp->role;
$metro = $emp->metro;
$emp_age = $emp->emp_age;
$designation = $emp->designation;
$tax_location = $emp->professional_tax_location;
$income_tax_type = 2;
if ((int) $emp_age === 0) {
$emp_age = 30; //default general people
}
$pt_projection_formula = $formula_result[$emp_role]->payroll_formula;
$pf_proj_formula = $pf_proj_result[$emp_role]->payroll_formula;
//Check Exist Query
/* $exist_qry = 'SELECT count(*) as count_val,prime_tax_calculation_id from cw_tax_calculation where process_month = "' . $process_month . '" and emp_code = "' . $employee_code . '" 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();
$exist_rows = $exist_result[0]->count_val;
$insert_id = $exist_result[0]->prime_tax_calculation_id; */
/** 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 FROM cw_transactions WHERE cw_transactions.trans_status = 1 and process_month = "' . $process_month . '" and employee_code = "' . $employee_code . '"';
//echo $pt_proj_query; die;
$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;
}
//echo $pt_proj_amt; die;
/** Get PT Projection value END **/
/** Earning Part Start - BSK **/
$tr_line = "";
$earned_array = array();
$proj_earn_pdf = array();
$act_earn_pdf = 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;
$final_tax_pay_month = 0;
$tax_insert_key = "";
$tax_insert_value = "";
$tax_update_qry = "";
$edu_cess_line = "";
$tax_cess_line = "";
$margin_relief_line = "";
$surcharge_pct_line = "";
$refund_line = "";
$relief_line = "";
$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");
//echo "BSK $month_date <br/>";
if (strtotime($month_date) <= strtotime($process_date)) { //For Actual
$formula = str_replace('@', '', $formula_array[$i]);
if ($projection_column) {
//$proj_query = 'SELECT '.$projection_column.' FROM cw_employees WHERE cw_employees.trans_status = 1 and employee_code = "'.$employee_code.'"';
/*$proj_query = 'SELECT '.$projection_column.' FROM cw_transactions WHERE cw_transactions.trans_status = 1 and transactions_month = "'.$month.'" and employee_code = "'.$employee_code.'"';
$proj_data = $this->db->query("CALL sp_a_run ('SELECT','$proj_query')");
$proj_result = $proj_data->result();
$proj_data->next_result();*/
//$proj = $proj_result[0]->$projection_column;
$proj = $trans_array[$employee_code][$projection_column];
} else {
$proj = 0;
}
$proj_earn_pdf[$value] = $proj;
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;
$earned_val = $earnings_result[0]->$value;
$pt_value = $earnings_result[0]->$professional_tax_db;
$act_earn_pdf[$value][$month] = $act_val;
$total_actual = $total_actual + $earned_val; //newly inserted for tax 21FEB2020
//Professional Tax Actual
$pt_actual = $pt_actual + $pt_value;
//echo "BSK $pt_actual :: $pt_value <br/>";
} else { //For Projection
$count++;
}
}
//die;
$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;
} else if ($gross_section === "2") {
$gross_2 = $gross_2 + $total_earn;
} else if ($gross_section === "3") {
$gross_3 = $gross_3 + $total_earn;
}
if ($total > 0) {
$tr_line .= "<tr><td>$view_array[$i]</td><td>$total_actual</td><td>$total_proj</td><td>$total</td></tr>";
} else if ($total < 0) {
$tr_line .= "<tr><td>$view_array[$i]</td><td>$total_actual</td><td>$total_proj</td><td>($total)</td></tr>";
}
$i++;
//echo "BSK $projection_column :: $proj :: $employee_code <br/>";
$earned_array[$value] = $total;
}
//die;
$proj_count_pdf = array();
foreach ($actual_months as $actual) {
$month = $actual->format("m-Y");
foreach ($act_earn_pdf as $act_pdf => $act_val) {
//$total_array['total'][$month] += $act_val[$month];
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;
}
}
}
}
//monthwise projection pdf generation updates
$annex_pdf_check = array_merge_recursive($act_earn_pdf, $proj_count_pdf);
$other_income_val = 0;
foreach ($gross_other_income_result as $gross_key => $gross_value) {
$income_type = $gross_value->income_type;
$income_description = $gross_value->income_description;
$other_income_column = $gross_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();
$other_income_val = $other_income_entry_result[0]->$other_income_column;
if ($other_income_val > 0) {
$tr_line .= "<tr><td>$income_description ($income_type)</td><td></td><td></td><td>$other_income_val</td></tr>";
$total_earn = $total_earn + $other_income_val;
}
}
//echo"BSK".$tr_line;
//die;
$gross_total = number_format((float) $total_earn, 2, '.', '');
$tot_tr_line = "<tr><td style='font-weight: bold;'><span style='color:green;'>GROSS Total</span></td><td></td><td></td><td style='font-weight: bold;'><span style='color:green;'>$gross_total</span></td></tr>";
/** Earning Part END - BSK **/
//insert query collecting
$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 . '","' . $emp_role . '","' . $employee_code . '","' . $process_month . '","' . $gross_total . '","' . $gross_1 . '","' . $gross_2 . '","' . $gross_3 . '"';
//updated query collecting
$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 . '"';
if ((int) $income_tax_type === 2) {
/** SECTION 10 Part START - BSK **/
$dec_value = 0;
$sec10_upd_query = "";
$sec10_line = "<tr><td style='font-weight: bold;font-size: 16px;'>Total Allowance to the extent exempt under Section 10</td><td style='font-size: 12px;'>Declare</td><td style='font-size: 12px;'>Limit</td><td></td></tr>";
$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) {
$proof_query = 'select ' . $tax_subsection_column . '_proof FROM `cw_tax_proof_entry` WHERE trans_status = 1 and emp_code = "' . $employee_code . '"';
$proof_data = $this->db->query("CALL sp_a_run ('SELECT','$proof_query')");
$proof_result = $proof_data->result();
$proof_data->next_result();
$dec_value = $proof_result[0]->$tax_subsection_column;
} else {
$dec_query = 'select ' . $tax_subsection_column . ' FROM `cw_declaration_entry` WHERE effective_month = "' . $process_month . '" and trans_status = 1 and emp_code = "' . $employee_code . '"';
$dec_data = $this->db->query("CALL sp_a_run ('SELECT','$dec_query')");
$dec_result = $dec_data->result();
$dec_data->next_result();
$dec_value = $dec_result[0]->$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) {
$proof_hra_query = 'select tax_house_rent FROM `cw_tax_proof_entry` WHERE trans_status = 1 and emp_code = "' . $employee_code . '"';
$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();
$house_rent = $proof_hra_result[0]->tax_house_rent;
} else {
$dec_query = 'select tax_house_rent FROM `cw_declaration_entry` WHERE effective_month = "' . $process_month . '" and trans_status = 1 and emp_code = "' . $employee_code . '"';
$dec_data = $this->db->query("CALL sp_a_run ('SELECT','$dec_query')");
$dec_result = $dec_data->result();
$dec_data->next_result();
$house_rent = $dec_result[0]->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);
}
//echo $hra_formula;die;
if ($hra_formula) {
$hra_formula = $eq->solveIF($hra_formula);
}
if ($hra_formula) {
$hra_res = $hra_formula;
} else {
$hra_res = 0;
}
//echo "BSK $house_rent :: $metro :: $hra_formula :: $pct_metro"; die;
if ($house_rent) {
$actual_rent = $hra_res * ($actual_rent / 100);
$rent = $house_rent - $actual_rent;
$metro_pct = 0;
//echo "BSK $rent :: $house_rent :: $actual_rent :: $metro :: $hra_formula :: $pct_metro"; die;
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);
//insert query collecting
$tax_insert_key .= ",hra";
$tax_insert_value .= ',"' . $sec10_least_value . '"';
//updated query collecting
$tax_update_qry .= ',hra = "' . $sec10_least_value . '"';
$sec10_line .= "<tr><td> $tax_subsection_name</td><td></td><td></td><td>$sec10_least_value</td></tr>";
$sec10_line .= "<tr><td> Calculations</td><td></td><td></td><td></td></tr>";
$sec10_line .= "<tr><td> A) $metro_pct% Basic </td><td>$metro_rslt</td><td></td><td></td></tr>";
$sec10_line .= "<tr><td> B) HRA Received </td><td>$earned_hra</td><td></td><td></td></tr>";
$sec10_line .= "<tr><td> C) Excess of Rent Paid Over $actual_rent_pct% Salary</td><td>$rent</td><td></td><td></td></tr>";
} else {
$sec10_line .= "<tr><td> $tax_subsection_name</td><td>$tax_subsection_limit</td><td>$dec_value</td><td>$sec10_least_value</td></tr>";
}
}
} 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, '.', '');
}
//insert query collecting
//$tax_insert_key .= ','.$tax_subsection_column;
//$tax_insert_value .= ',"'.$sec10_least_value.'"';
//updated query collecting
//$tax_update_qry .= ','.$tax_subsection_column.' = "'.$sec10_least_value.'"';
if ($sec10_least_value > 0) {
$sec10_line .= "<tr><td> $tax_subsection_name</td><td>$dec_value</td><td>$tax_subsection_limit</td><td>$sec10_least_value</td></tr>";
}
}
$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.'"';
//updated query collecting
//$tax_update_qry .= ',tot_'.$tax_section_column.' = "'.$total_sec10.'"';
}
//insert query collecting
$tax_insert_key .= ',tot_' . $tax_section_column;
$tax_insert_value .= ',"' . $total_sec10 . '"';
//updated query collecting
$tax_update_qry .= ',tot_' . $tax_section_column . ' = "' . $total_sec10 . '"';
$sec10_line .= "<tr style='font-weight: bold;'><td>Total $sec10_tax_section</td><td></td><td></td><td>$total_sec10</td></tr>";
$gross_total = $gross_total - $total_sec10;
$gross_total = number_format((float) $gross_total, 2, '.', '');
//insert query collecting
$tax_insert_key .= ',gross_tot_taxsec_1';
$tax_insert_value .= ',"' . $gross_total . '"';
//updated query collecting
$tax_update_qry .= ',gross_tot_taxsec_1 = "' . $gross_total . '"';
$sec10_line .= "<tr style='font-weight: bold;'><td><span style='color:green;'>GROSS Total</span></td><td></td><td></td><td><span style='color:green;'>$gross_total</span></td></tr>";
/** SECTION 10 Part END - BSK **/
/** SUB SECTIONS Part START - BSK **/
$sub_section_line = "";
$sub_section_upd_query = "";
$gross_upd_query = "";
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_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
$section_dec_query = 'SELECT ' . $tax_subsection_column . ' FROM `cw_declaration_entry` WHERE effective_month = "' . $process_month . '" and trans_status = 1 and emp_code= "' . $employee_code . '"';
$section_dec_data = $this->db->query("CALL sp_a_run ('SELECT','$section_dec_query')");
$section_dec_result = $section_dec_data->result();
$section_dec_data->next_result();
$dec_value = $section_dec_result[0]->$tax_subsection_column;
$sub_least_value = min($dec_value, $tax_subsection_limit);
//$sub_least_value = min($dec_value, $tot_limit);
//insert query collecting
$tax_insert_key .= ',' . $tax_subsection_column;
$tax_insert_value .= ',"' . $sub_least_value . '"';
//updated query collecting
$tax_update_qry .= ',' . $tax_subsection_column . ' = "' . $sub_least_value . '"';
if ($tax_subsection_column === "taxsubsec_8") {
$total_sub = $total_sub + $tax_subsection_limit;
//if($tax_subsection_limit > 0){
$sub_section_line .= "<tr><td> $tax_subsection_name</td><td></td><td></td><td>$tax_subsection_limit</td></tr>";
//}
} else if ($tax_subsection_column === "taxsubsec_10") {
/** Get PT Projection value START **/
$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;
//echo "BSK $pt_actual + $pt_proj"; die;
$pt_total = number_format((float) $pt_total, 2, '.', '');
$total_sub = $total_sub + $pt_total;
$total_sub = number_format((float) $total_sub, 2, '.', '');
if ($pt_total > 0) {
$sub_section_line .= "<tr><td> $tax_subsection_name</td><td>$pt_actual</td><td>$pt_proj</td><td>$pt_total</td></tr>";
}
} 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 ($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;
//if($tax_subsection_limit>0){
$sub_section_line .= "<tr><td> $tax_subsection_name</td><td>$pf_total</td><td>$tax_subsection_limit</td><td></td></tr>";
//}
} else {
if ($match_value > 0) {
$sub_section_line .= "<tr><td> $tax_subsection_name</td><td>$match_value</td><td>$tax_subsection_limit</td><td>$sub_least_value</td></tr>";
}
}
} else {
$total_sub = $total_sub + $sub_least_value;
$tot_limit = $tot_limit + $tax_subsection_limit;
$tot_deduct = $tot_deduct + $dec_value;
if (($dec_value === "") && ($dec_value === "0.00")) {
$sub_section_line .= "";
} else {
//if($sub_least_value>0){
$sub_section_line .= "<tr><td> $tax_subsection_name</td><td>$dec_value</td><td>$tax_subsection_limit</td><td>$sub_least_value</td></tr>";
//}
}
}
}
}
if ($key === "Under Section 80C") {
$total_sub = min($tot_deduct, $section_limit);
$total_sub = number_format((float) $total_sub, 2, '.', '');
}
//insert query collecting
$tax_insert_key .= ',tot_' . $tax_section_column;
$tax_insert_value .= ',"' . $total_sub . '"';
//updated query collecting
$tax_update_qry .= ',tot_' . $tax_section_column . ' = "' . $total_sub . '"';
$sub_section_line .= "<tr style='font-weight: bold;'><td>Total $key</td><td></td><td></td><td><span style='color:#000;'>$total_sub</span></td></tr>";
$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 . '"';
//updated query collecting
$tax_update_qry .= ',gross_tot_' . $tax_section_column . ' = "' . $gross_total . '"';
$sub_section_line .= "<tr style='font-weight: bold;'><td><span style='color:green;'>GROSS Total</span></td><td></td><td></td><td><span style='color:green;'>$gross_total</span></td></tr>";
}
}
// 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;
$add_income_line = "<tr><td style='font-weight: bold;font-size: 16px;'>ADD Income</td><td style='font-size: 12px;'></td><td style='font-size: 12px;'></td><td></td></tr>";
$less_income_line = "<tr><td style='font-weight: bold;font-size: 16px;'>Less Income</td><td style='font-size: 12px;'></td><td style='font-size: 12px;'></td><td></td></tr>";
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;
if ((int) $income_type_id === 1) {
$tot_add = $tot_add + $income_val;
if ($income_val > 0) {
$add_income_line .= "<tr><td>$income_description </td><td></td><td>$income_val</td><td></td></tr>";
}
} else {
$tot_less = $tot_less + $income_val;
if ($income_val > 0) {
$less_income_line .= "<tr><td>$income_description </td><td></td><td>$income_val</td><td></td></tr>";
}
}
}
if ($tot_add) {
$gross_total = $gross_total + $tot_add;
$add_income_line .= "<tr style='font-weight: bold;'><td>Total ADD Income</td><td></td><td></td><td>$tot_add</td></tr>";
}
if ($tot_less) {
$gross_total = $gross_total - $tot_less;
$less_income_line .= "<tr style='font-weight: bold;'><td>Total Less Income</td><td></td><td></td><td>$tot_less</td></tr>";
}
$gross_total = $gross_total - $total_less_income;
$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 = "' . $gross_total . '"';
$less_section_line = "<tr style='font-weight: bold;'><td><span style='color:green;'>GROSS Total</span></td><td></td><td></td><td><span style='color:green;'>$gross_total</span></td></tr>";
$gross_total = ceil($gross_total / 10) * 10; //10 Rupee Rounding
$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 = "' . $final_value . '"';
$final_line = "<tr style='font-weight: bold;'><td><span style='color:green;'>Total Income (Round By 10 Rupess)</span></td><td></td><td></td><td><span style='color:green;'>$final_value</span></td></tr>";
/** SUB SECTIONS Part END - BSK **/
if ((int) $income_tax_type === 2) {
/** 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_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 min_age <= "' . $emp_age . '" and max_age >= "' . $emp_age . '" 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();
$tax_slab_line = "";
$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;
//if($tax_amount > 0){
$tax_slab_line .= "<tr><td> Tax Calculation<br/> Exemption Rs. $tax_range_end and the Balance Amount</td><td> <br/>$final_tax</td><td></td><td></td></tr>";
//}
} else {
$final_tax = 0;
}
} else {
if ((int) $final_tax > 0 && $final_value >= $tax_range_start && $final_value >= $tax_range_end) {
//$tax_range_txt=($tax_range_end-$tax_range_start)+1;
$tax_value = ($tax_range_start * $tax_amount) / 100;
$final_tax = $final_tax - $tax_range_start;
$tax_slab_line .= "<tr><td> For $tax_range_start : $tax_amount% Tax Amount</td><td>$tax_value</td><td></td><td></td></tr>";
} else if ((int) $final_tax > 0 && $final_value >= $tax_range_start && $final_value <= $tax_range_end) {
$tax_value = ($final_tax * $tax_amount) / 100;
$tax_slab_line .= "<tr><td> For $final_tax : $tax_amount% Tax Amount</td><td>$tax_value</td><td></td><td></td></tr>";
$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, '.', '');
}
$tot_tax_line = "<tr><td><span style='font-weight: bold;'>Tax on Total Income</td><td></td><td></td><td>$total_tax</td></tr>";
//insert query collecting
$tax_insert_key .= ',slab_type,total_tax';
$tax_insert_value .= ',"' . $slap_type . '","' . $final_value . '"';
//updated query collecting
$tax_update_qry .= ',slab_type = "' . $slap_type . '",total_tax = "' . $final_value . '"';
} else { // Old Tax End
/** TAX SLAB Part START - BSK **/
//Get New TAX Slab
$final_value = $gross_total;
//echo "BSK $final_value"; die;
$tax_slab_query = 'SELECT tax_range_start,tax_range_end,tax_amount_percentage,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_slap_type on cw_slap_type.prime_slap_type_id = cw_income_tax_slab_new_income_tax_slab_details_new.prime_income_tax_slab_new_id inner join cw_income_tax_slab_new on cw_income_tax_slab_new.slab_type = cw_slap_type.prime_slap_type_id WHERE min_age <= "' . $emp_age . '" and max_age >= "' . $emp_age . '" and cw_income_tax_slab_new_income_tax_slab_details_new.trans_status = 1';
//echo $tax_slab_query; die;
$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();
$tax_slab_line = "";
$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_percentage;
$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;
//if($tax_amount > 0){
$tax_slab_line .= "<tr><td> Tax Calculation<br/> Exemption Rs. $tax_range_end and the Balance Amount</td><td> <br/>$final_tax</td><td></td><td></td></tr>";
//}
} else {
$final_tax = 0;
}
} else {
if ((int) $final_tax > 0 && $final_value >= $tax_range_start && $final_value >= $tax_range_end) {
//$tax_range_txt=($tax_range_end-$tax_range_start)+1;
$tax_range_start = $tax_range_end - $tax_range_start;
$tax_value = ($tax_range_start * $tax_amount) / 100;
$final_tax = $final_tax - $tax_range_start;
$tax_slab_line .= "<tr><td> For $tax_range_start : $tax_amount% Tax Amount</td><td>$tax_value</td><td></td><td></td></tr>";
} else if ((int) $final_tax > 0 && $final_value >= $tax_range_start && $final_value <= $tax_range_end) {
$tax_range_start = $tax_range_end - $tax_range_start;
$tax_value = ($final_tax * $tax_amount) / 100;
$tax_slab_line .= "<tr><td> For $final_tax : $tax_amount% Tax Amount</td><td>$tax_value</td><td></td><td></td></tr>";
//echo "BSK $tax_range_start :: $tax_value :: $final_tax :: $tax_amount :: $tax_range_end :: $total_tax <br/>";
$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, '.', '');
}
$tot_tax_line = "<tr><td><span style='font-weight: bold;'>Tax on Total Income</td><td></td><td></td><td>$total_tax</td></tr>";
//insert query collecting
$tax_insert_key .= ',slab_type,total_tax';
$tax_insert_value .= ',"' . $slap_type . '","' . $final_value . '"';
//updated query collecting
$tax_update_qry .= ',slab_type = "' . $slap_type . '",total_tax = "' . $final_value . '"';
} // New Tax End
//$margin_relief = $this->margin_relief_amt(5100000,1342500);
$margin_relief = $this->margin_relief_amt($final_value, $total_tax);
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) {
$surcharge_pct_line = "<tr><td> Surcharge Add ($surcharge_pct %)</td><td></td><td></td><td>$surcharge_charge</td></tr>";
if ((int) $margin_relief_amt !== 0) {
$margin_relief_line = "<tr><td> Margin Relief Fund</td><td></td><td></td><td>$margin_relief_amt</td></tr>";
//insert query collecting
$tax_insert_key .= ',margin_relief';
$tax_insert_value .= ',"' . $margin_relief_amt . '"';
//updated query collecting
$tax_update_qry .= ',margin_relief = "' . $margin_relief_amt . '"';
}
//insert query collecting
$tax_insert_key .= ',surcharge_amt';
$tax_insert_value .= ',"' . $surcharge_charge . '"';
//updated query collecting
$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_line = "<tr><td> Less:87A of the Income Tax Act, 1961 rebate</td><td></td><td></td><td>$total_tax</td></tr>";
$refund_sts = 1;
//insert query collecting
$tax_insert_key .= ',rebate_amt';
$tax_insert_value .= ',"' . $total_tax . '"';
//updated query collecting
$tax_update_qry .= ',rebate_amt = "' . $total_tax . '"';
}
//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, '.', '');
//insert query collecting
$tax_insert_key .= ',edu_cess';
$tax_insert_value .= ',"' . $cess_value . '"';
//updated query collecting
$tax_update_qry .= ',edu_cess = "' . $cess_value . '"';
}
$edu_cess_line .= "<tr><td> Education Cess $edu_cess % of $total_tax </td><td></td><td></td><td>$cess_value</td></tr>";
//relief under section 89 values
$relief_line = "<tr><td> Relief under section 89</td><td></td><td></td><td></td></tr>";
//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, '.', '');
//$gross_total = $gross_total + $cess_tax;
//Final amount total is
$tax_cess_line .= "<tr style='font-weight: bold;'><td>Tax Payable including Education Cess minus of Relief under section 89</td><td></td><td></td><td>$cess_tax</td></tr>";
//insert query collecting
$tax_insert_key .= ',tax_amt';
$tax_insert_value .= ',"' . $cess_tax . '"';
//updated query collecting
$tax_update_qry .= ',tax_amt = "' . $cess_tax . '"';
//$tax_cess_line .= "<tr style='font-weight: bold;color:green'><td>GROSS Total</td><td></td><td></td><td>$gross_total</td></tr>";
}
$month_tds_val = "NIL";
$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
$start_pre_text = date("d-m-Y", strtotime($start_date));
$end_pre_text = date("d-m-Y", strtotime($end_date));
$prev_tax = 0;
/*$pre_tax_qry = 'select IFNULL((sum(monthly_tds)),0) as prev_tax 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") and employee_code="'.$employee_code.'"';
$pre_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$pre_tax_qry')");
$pre_tax_result = $pre_tax_data->result();
$pre_tax_data->next_result();
$prev_tax = $pre_tax_result[0]->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 ($prev_tax) {
$prev_tax_line = "<tr><td>Tax Deducted At Source u/s 192(1)</td><td></td><td></td><td>$prev_tax</td></tr>";
}
$tax_pay_line = "<tr><td>Tax Payable / Refundable</td><td></td><td></td><td>$final_tax_pay_month</td></tr>";
//finding values
$tax_count_line = "<tr><td>TDS to be deducted for $month_count_rslt Months, Per Month Value is</td><td></td><td></td><td>$month_tds_val</td></tr>";
$tax_current_line = "<tr><td>Total Tax To Be Paid This Month $month_name</td><td></td><td></td><td>$month_tds_val</td></tr>";
//insert query collecting
$tax_insert_key .= ',final_tax_amt';
$tax_insert_value .= ',"' . $month_tds_val . '"';
//updated query collecting
$tax_update_qry .= ',final_tax_amt = "' . $month_tds_val . '"';
//echo $tr_line; die;
//if((int)$annexure_sts_val === 1){
//do later
//$tbl_data = $this->get_yearly_annex($annex_pdf_check,$employee_code,$view_det,$emp_name,$designation);
//$pdf_data[$employee_code] = $tbl_data;
//}else{
//TDS to be deducted for 9 Months, Per Month Value is
//Total Tax To Be Paid This Month
$table_data = "<div style='width:700px; margin-left: auto; margin-right: auto; padding: 15px; background-color: #EEEEEE; border-radius: 3px; box-shadow: 0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2);'>
<table style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;text-align: left;font-size: 14px;background-color: #FFFFFF;' border='1' cellspacing='0' cellpadding='4'>
<tbody>
<tr>
<td colspan='2'>
<h3 style='color:#d3434d;margin:10px;font-size:21px;text-align: center;'>TDS WORK SHEET</h3>
</td>
</tr>
<tr>
<td>Name and address of the employer</td>
<td>Code,name and designation of the employee</td>
</tr>
<tr>
<td style='font-weight:bold;'>$company_name<br/>$company_address<br/>$company_city<br/>$company_state<br/>$company_country</td>
<td>$employee_code<br/>$emp_name<br/>$designation<br/>$start_text - $end_text<br/>Declaration Wise</td>
</tr>
<tr>
<td colspan='2'><h3 style='color:#d3434d;margin:10px;font-size:16px;text-align: center;'>WORKSHEET FOR THE MONTH OF " . strtoupper(date("M-Y", strtotime("01-" . $this->input->post('process_month')))) . "</h3></td>
</tr>
</tbody>
</table>
<h5 style='text-align: center;'>DETAILS OF SALARY PAID AND ANY OTHER INCOME AND TAX DEDUCTED</h5>
<table style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;font-size: 14px;background-color:#FFFFFF;' border='1' cellspacing='0' cellpadding='4'>
<tbody>
<tr style='background-color:#828181;color: #000000;font-weight:bold;'>
<td>Components</td>
<td>Actual</td>
<td>Projection</td>
<td>Total</td>
</tr>
$tr_line
$add_other_income_line
$tot_tr_line
$sec10_line
$sub_section_line
$add_income_line
$less_income_line
$less_section_line
$final_line
$tax_slab_line
$tot_tax_line
$refund_line
$surcharge_pct_line
$margin_relief_line
$edu_cess_line
$relief_line
$tax_cess_line
$prev_tax_line
$tax_pay_line
$tax_count_line
$tax_current_line
</tbody>
</table>
</div>";
$table_data = "<!DOCTYPE html><html> <body>" . $table_data . "</body></html>";
$pdf_data[$employee_code] = $table_data;
//}
//Check Tax Calculation Exist Query
$exist_tax_qry = 'select count(*) as count_val,prime_tax_calculation_id from cw_tax_calculation where process_month = "' . $process_month . '" and emp_code = "' . $employee_code . '" and trans_status = 1';
$exist_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_tax_qry')");
$exist_tax_result = $exist_tax_data->result();
$exist_tax_data->next_result();
$exist_rows = $exist_tax_result[0]->count_val;
$tax_calculation_id = $exist_tax_result[0]->prime_tax_calculation_id;
//echo "<pre>";
//echo $tax_insert_key;
//echo "</pre>";
//die;
if ((int) $exist_rows === 0) {
$tax_insert_qry = 'INSERT into cw_tax_calculation (' . $tax_insert_key . ') values (' . $tax_insert_value . ')';
$tax_insert_info = $this->db->query("CALL sp_a_run ('INSERT','$tax_insert_qry')");
$tax_insert_result = $tax_insert_info->result();
$tax_insert_info->next_result();
} else {
$update_query = 'UPDATE cw_tax_calculation SET ' . $tax_update_qry . ',trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '" WHERE prime_tax_calculation_id = "' . $tax_calculation_id . '" and trans_status = 1';
$update_result = $this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
}
$monthly_input_upd_qry = 'UPDATE cw_monthly_input SET monthly_tds = "' . $month_tds_val . '" WHERE employee_code = "' . $employee_code . '" and process_month = "' . $process_month . '" and trans_status = 1';
$monthly_input_upd_result = $this->db->query("CALL sp_a_run ('UPDATE','$monthly_input_upd_qry')");
}
$count = count($pdf_data);
echo json_encode(array(
'success' => TRUE,
'pdf_data' => $pdf_data,
'count' => $count
));
}
}
public function generate_pdf()
{
$employee_code = $this->input->post('code');
$content = $this->input->post('html');
$process_month = $this->input->post('process_month');
// Load HTML content
$this->dompdf->loadHtml($content);
// (Optional) Setup the paper size and orientation
$this->dompdf->setPaper('A4', 'portrait');
// Render the HTML as PDF
$this->dompdf->render();
// Output the generated PDF (1 = download and 0 = preview)
$output = $this->dompdf->output();
//new permission folder
$folder = "worksheet";
$folder1 = $process_month;
$pdf_name = $employee_code;
$final_html = $output;
$oldmask = umask(0);
if(!file_exists($folder)){
mkdir($folder, 0777, true);
}
if(!file_exists($folder."/".$folder1)){
mkdir($folder."/".$folder1, 0777, true);
}
//Check File Exist
if(file_exists($folder."/".$folder1."/".$pdf_name.".pdf")){
chmod($folder."/".$folder1."/".$pdf_name.".pdf", 0777);
unlink($folder."/".$folder1."/".$pdf_name.".pdf");
}
file_put_contents($folder."/".$folder1."/".$pdf_name.".pdf", $final_html);
chmod($folder."/".$folder1."/".$pdf_name.".pdf", 0777);
umask($oldmask);
$path = $folder."/".$folder1."/".$pdf_name.".pdf";
chmod($path, 0777, true);
echo $path;
}
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;
}
//UPDATE STATUS TO DELETE IN MODULE PRIMARY TABLE
public function delete()
{
$delete_ids = implode(",", $this->input->post('delete_ids'));
$can_process = TRUE;
$delete_status = FALSE;
if ($this->check_delete_status()) {
$delete_status = TRUE;
$check_table_query = 'SELECT GROUP_CONCAT(prime_module_id) as prime_module_id,GROUP_CONCAT(label_name) as label_name from cw_form_setting WHERE pick_table = "' . $this->prime_table . '" and trans_status = 1 ';
$check_table_info = $this->db->query("CALL sp_a_run ('SELECT','$check_table_query')");
$check_table_rlst = $check_table_info->row();
$check_table_info->next_result();
if ($check_table_rlst->prime_module_id) {
$prime_module_id = explode(",", $check_table_rlst->prime_module_id);
$label_name = explode(",", $check_table_rlst->label_name);
$i = 0;
$select_table = '';
$select_label = '';
$select_trans_status = '';
$select_where = '';
foreach ($prime_module_id as $check_modules) {
$table_name = "cw_" . $check_modules;
$table_rename = $table_name . "_$i";
$select_table .= "$table_rename.$label_name[$i],";
$select_label .= " $table_name $table_rename,";
if ((int) $i === 0) {
$select_trans_status .= "( $table_rename.trans_status = 1";
$select_where .= " and ($table_rename.$label_name[$i] in ($delete_ids)";
} else {
$select_trans_status .= " and $table_rename.trans_status = 1";
$select_where .= " or $table_rename.$label_name[$i] in ($delete_ids)";
}
$i++;
}
$select_trans_status .= ")";
$select_where .= ")";
$select_table = rtrim($select_table, ',');
$select_label = rtrim($select_label, ',');
$check_module_query .= 'SELECT ' . $select_table . ' from ' . $select_label . ' WHERE ' . $select_trans_status . ' ' . $select_where . ' LIMIT 0,1';
$check_module_info = $this->db->query("CALL sp_a_run ('SELECT','$check_module_query')");
$values_count = $check_module_info->num_rows();
$check_module_info->next_result();
if ((int) $values_count > 0) {
$can_process = False;
$delete_status = False;
}
}
if ($delete_status) {
$delete_query = 'DELETE FROM ' . $this->prime_table . ' WHERE ' . $this->prime_id . ' in (' . $delete_ids . ')';
if ($this->db->query("CALL sp_a_run ('RUN','$delete_query')")) {
$row_set_query = 'SELECT form_view_label_name from cw_form_view_setting where form_view_type = "3" and prime_view_module_id = "' . $this->control_name . '" and trans_status = 1';
$row_set_info = $this->db->query("CALL sp_a_run ('SELECT','$row_set_query')");
$row_count = (int) $row_set_info->num_rows();
$row_set_info->next_result();
if ($row_count !== 0) {
$row_set_result = $row_set_info->result();
$delete_table_name = '';
$delete_table_condition = '';
foreach ($row_set_result as $row_set) {
$row_set_table_name = "cw_" . $this->control_name . "_" . $row_set->form_view_label_name;
$delete_table_name .= "$row_set_table_name,";
$delete_table_condition .= " $row_set_table_name.$this->prime_id in ('$delete_ids') and";
}
$delete_table_name = rtrim($delete_table_name, ',');
$delete_table_condition = rtrim($delete_table_condition, 'and');
$delete_row_set_query = 'DELETE FROM ' . $delete_table_name . ' WHERE ' . $delete_table_condition . '';
$this->db->query("CALL sp_a_run ('RUN','$delete_row_set_query')");
}
$can_process = False;
}
}
}
if ($can_process) {
$created_on = date("Y-m-d h:i:s");
$prime_upd_query .= 'trans_deleted_by = "' . $this->logged_id . '",trans_deleted_date = "' . $created_on . '"';
$prime_update_query = 'UPDATE ' . $this->prime_table . ' SET trans_status = 0,' . $prime_upd_query . ' WHERE ' . $this->prime_id . ' in (' . $delete_ids . ')';
if ($this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')")) {
echo json_encode(array(
'success' => TRUE,
'message' => "Successfully Deleted"
));
} else {
echo json_encode(array(
'success' => FALSE,
'message' => "Unable to delete"
));
}
} else if ($delete_status) {
echo json_encode(array(
'success' => TRUE,
'message' => "Successfully Deleted"
));
} else {
$modules = ucwords($check_table_rlst->prime_module_id);
echo json_encode(array(
'success' => FALSE,
'message' => "Unable to delete, This value is already used in $modules modules"
));
}
}
//CHECK UNIQUE FIELD STATUS
public function check_delete_status()
{
$check_delete_query = 'SELECT GROUP_CONCAT(unique_field) as unique_field from cw_form_setting WHERE prime_module_id = "' . $this->control_name . '" and trans_status = 1 ';
$check_delete_info = $this->db->query("CALL sp_a_run ('SELECT','$check_delete_query')");
$check_delete_rlst = $check_delete_info->row();
$check_delete_info->next_result();
$unique_info = explode(",", $check_delete_rlst->unique_field);
if (in_array('1', $unique_info)) {
return TRUE;
} else {
return FALSE;
}
}
//UPDATE STATUS TO DELETE FOR UPLOAD FILES or DOCUMENTS
public function remove_file()
{
$prime_id_val = $this->input->post('prime_id_val');
$is_defult = (int) $this->input->post('is_defult');
$input_name = $this->input->post('input_name');
$table_name = '';
if ($is_defult === 1) {
$table_name = $this->prime_table;
} else if ($is_defult === 2) {
$table_name = $this->cf_table;
}
if ($table_name) {
$created_on = date("Y-m-d h:i:s");
$set_query = $input_name . ' = "" ,trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '"';
$update_query = 'UPDATE ' . $table_name . ' SET ' . $set_query . ' WHERE ' . $this->prime_id . ' = "' . $prime_id_val . '"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
echo json_encode(array(
'success' => TRUE,
'message' => "Successfully updated"
));
} else {
echo json_encode(array(
'success' => FALSE,
'message' => "Unable to process your request"
));
}
}
//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
));
}
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;
}
//calculating margin relief for above 50laks and 1Cro but margin relief no negative values
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)
{
//echo "BSK $pt_proj_amt,$tax_location,$process_month,$count,$employee_code,$professional_tax_db"; die;
$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;
//echo $ptax_qry; die;
$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"));
if ($last_month_date === $process_month_date) {
$previous_tax_qry = 'select ifnull(sum(total_earn),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(total_earn),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")';
}
//echo $previous_tax_qry; die;
$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);
//echo $find_tax_qry; die;
$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_pf_projection($pf_proj_formula, $count, $employee_code)
{
require_once "./dist/eos-1.0.0/eos.class.php";
$eq = new eqEOS();
if ($pf_proj_formula) {
$preg_match = preg_match_all('#\@(.*?)\@#', $pf_proj_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 trans_status = 1 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;
$pf_proj_formula = str_replace($find_value, $for_value, $pf_proj_formula);
}
if ($pf_proj_formula) {
$pf_proj_value = $eq->solveIF($pf_proj_formula);
}
return $pf_proj_value * $count;
}
}
//designed array for $earyly['01-05-2019']['earned_hra'];
public function get_yearly_annex($annex_pdf_check, $emp_code, $view_det, $emp_name, $designation)
{
//Actual data count and datas
//Projection data count and datas
$start_date = $this->financial_info[0]->start_date;
$end_date = $this->financial_info[0]->end_date;
$start_text = date("d-M-Y", strtotime($start_date));
$end_text = date("d-M-Y", strtotime($end_date));
$begin = new DateTime($start_date);
$end = new DateTime($end_date);
$month_td = "";
$arr = array(
"total" => "Total"
);
while ($begin <= $end) {
$month_list = $begin->format('M');
$month = $begin->format('m-Y');
echo $begin->modify('first day of next month');
$month_td .= "<td><b>$month_list</b></td>";
$arr[$month] = "";
}
$month_td_list = "<tr><td><b>Earnings</b></td>$month_td</tr>";
$tr_line = "";
foreach ($annex_pdf_check as $lable => $annex_val) {
if ($lable !== 'total') {
$view_lable = $view_det[$lable];
} else {
$view_lable = 'Total';
}
$td_line = "";
$total_array = array();
foreach ($annex_val as $ann_key => $ann_val) {
$arr[$ann_key] += $ann_val;
$td_line .= "<td>$ann_val</td>";
}
$tr_line .= "<tr><td>$view_lable</td>$td_line</tr>";
}
$final_td = "";
$final_tr = "";
foreach ($arr as $arr_key => $arr_val) {
if ($arr_val !== "Total") {
$arr_val = number_format((float) $arr_val, 2, '.', '');
}
$final_td .= "<td>$arr_val</td>";
}
$final_tr = "<tr style='font-weight:bold;'>$final_td</tr>";
//GET COMPANY DETAILS
$company_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_company_information` where cw_company_information.trans_status = 1')");
$company_result = $company_info->result();
$company_info->next_result();
$company_name = $company_result[0]->company_name;
$company_address = $company_result[0]->address;
$company_city = $company_result[0]->city;
$company_state = $company_result[0]->state;
$company_country = $company_result[0]->country;
$company_details = "<b>$company_name</b><br/><b>$company_address</b><br/><b>$company_city</b><br/><b>$company_state</b><br/><b>$company_country</b>";
$table_data = "<div style='width:700px; margin-left: auto; margin-right: auto; padding: 15px; background-color: #EEEEEE; border-radius: 3px; box-shadow: 0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2);'>
<table style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 1px solid black;text-align: left;font-size: 14px;background-color: #FFFFFF;' border='1' cellspacing='0' cellpadding='4'>
<tbody>
<tr>
<td colspan='5'>$company_details</td>
<td colspan='8'><b>Code: $emp_code</b><br/><b>Name: $emp_name</b><br/><b>Designation: $designation</b><br/><b>$start_text - $end_text</b></td>
</tr>
<tr>
$month_td_list
</tr>
$tr_line
$final_tr
</tbody>
</table>
</div>";
return $table_data;
}
//BSK START on 22July2020
public function formula($trans_array)
{
$input_query = 'SELECT * 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 line_input_bind_col !="" AND cw_form_condition_formula.trans_status=1 and cond_order != 0';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
//print_r($input_result); die;
$input_colum = "";
foreach ($input_result as $input) {
$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_array[\$key]['" . $out_colum . "'] = ", $input_colum);
$data .= "'$check_form' => $value,";
}
}
}
$formula_code .= "\n\t\t\t $input_colum \n\t\t\t";
}
$formula_code = "\n\t\t" . ' foreach($trans_array as $key => $trans){ ' . $formula_code . "\n\t\t } return \$trans_array; \n\t\t";
$dynamic_file_name = "tax_" . $this->logged_id . ".php";
unlink("$dynamic_file_name");
$fname = "tax(\$trans_array)";
$code = "<?php function $fname{ $formula_code }?>";
fopen("$dynamic_file_name", "w");
file_put_contents("$dynamic_file_name", $code);
chmod($dynamic_file_name, 0777);
require_once("$dynamic_file_name");
$final_result_array = tax($trans_array);
unlink("$dynamic_file_name");
return $final_result_array;
}
//BSK START on 22July2020
}
?>