File: /home/cafsindia/cloud_cafsinfotech_in/application/controllers/Income_tax_process.php
<?php
if (!defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Income_tax_process extends Action_controller{
public function __construct(){
parent::__construct('income_tax_process');
if (!$this->Appconfig->isAppvalid()){
redirect('config');
}
// Load pdf library
require_once "./dist/eos-1.0.0/eos.class.php";
$eq = new eqEOS();
$this->load->library('pdf');
}
// LOAD PAGE WITH TABLE DATA
public function index(){
//PAGE INFO FUNCTION
$this->page_info();
$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(){
$this->save_info();
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");
$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;
if(!$fin_set_id){
echo json_encode(array('success' => false, 'msg' => 'Financial Settings not Exist..'));
exit(0);
}
//Process Tax mode =1 and mode 2 delete the tax only delete call
$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;
//Check payroll 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, 'msg' => 'Income Tax Locked for this Month'));
exit(0);
}
$process_month_only = '01-'.$process_month;
$process_month_only = date('Y-m-d',strtotime($process_month_only));
$financial_exits_qry = 'select count(prime_financial_setting_id) as finance_exits_count from cw_financial_setting where start_date <= "'.$process_month_only.'" and end_date >= "'.$process_month_only.'" and set_as_default_financial_year = 1 and trans_status = 1';
$financial_exits_info = $this->db->query("CALL sp_a_run ('SELECT','$financial_exits_qry')");
$financial_exits_result = $financial_exits_info->result();
$financial_exits_info->next_result();
$finance_exits_count = $financial_exits_result[0]->finance_exits_count;
if((int)$finance_exits_count === 0){
echo json_encode(array('success' => false, 'msg' => 'Please Active Financial Year'));
exit(0);
}
$where_query = "";
$cond_query = "";
$section_dec_query = "";
$off_tds_qry = "";
$tax_where_query = "";
$off_where_query = "";
if ($process_type === 1) {
$where_query = ' and employee_code = "' . $process_emp_code . '" ';
$cond_query = ' and cw_employees.employee_code = "' . $process_emp_code . '" ';
$tax_where_query = ' emp_code = "' . $process_emp_code . '" ';
$off_where_query = ' employee_code = "' . $process_emp_code . '" ';
}else if ($process_type === 2) {
$where_query = ' and role = "' . $process_role . '"';
$cond_query = ' and cw_employees.role = "' . $process_role . '"';
$tax_where_query = ' category = "' . $process_role . '"';
$off_where_query = ' category = "' . $process_role . '"';
}
//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){
$prev_comp_tds_result_array = array_reduce($prev_comp_tds_result, function($result, $arr){
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
}
$off_tds_qry = 'SELECT amount,employee_code,category from cw_offline_tds_entry where '.$off_where_query.' and payroll_month = "'.$process_month.'" and trans_status = 1';
$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){
$off_tds_result_array = array_reduce($off_tds_result, function($result, $arr){
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
}
//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 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) {
if ($process_type === 1) {
$del_query = 'and emp_code = "' . $process_emp_code . '"';
}else if ($process_type === 2) {
$del_query = 'and emp_role = "' . $process_role . '"';
}
$delete_ins_query = 'INSERT INTO cw_tax_calculation_dlt SELECT * 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,
'msg' => "Data deleted successfully!"
));
exit(0);
}else{
$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);
}
$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;
//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 = $this->company_info[0]->company_name;
$company_address = $this->company_info[0]->address;
$company_city = $this->company_info[0]->city;
$company_state = $this->company_info[0]->state;
$company_country = $this->company_info[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;
}
//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 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,
'msg' => "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 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 label_name,field_type FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND input_view_type IN (1,2) ORDER BY prime_form_id';
$column_data = $this->db->query("CALL sp_a_run ('SELECT','$column_qry')");
$column_rslt = $column_data->result();
$column_data->next_result();
$columns = array_column($column_rslt, 'label_name');
$column_label = implode(',', $columns);
// $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;
// echo $emp_sal_query."<br>";
$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();
// echo "<pre>";
// print_r($emp_sal_rslt);die;
$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();
//print_r($formula_result);die;
$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');
$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 "BSK $pt_projection_formula";die;
//print_r($pt_projection_result);die;
$process_month_dt = "01-".$process_month;
$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 ' .$tax_where_query. ' 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 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();
$other_income_array = array_reduce($other_income_entry_result, function($result, $arr){
$result[$arr['emp_code']] = $arr;
return $result;
}, array());
if((int)$proof_wise_val === 1) {
$section_dec_query = 'SELECT * from cw_tax_proof_entry where finacial_setting_id = ' .$fin_set_id. ' and ' .$tax_where_query. ' and trans_status = 1';
}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 ' .$tax_where_query. ' and 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") 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();
$section_dec_array = array_reduce($section_dec_result, function($result, $arr){
$result[$arr['emp_code']] = $arr;
return $result;
}, array());
//Check Tax Calculation Exist Query
$exist_tax_qry = 'select count(*) as count_val,prime_tax_calculation_id,emp_code from cw_tax_calculation where process_month = "' . $process_month . '" and trans_status = 1 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();
$exist_tax_array = array_reduce($exist_tax_result, function($result, $arr){
$result[$arr['emp_code']] = $arr;
return $result;
}, array());
// 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();
//Get Earnings Query
$pf_match = "";
$earn_qry = "employee_code,transactions_month";
foreach ($earnings_columns as $key => $value){
$formula = str_replace('@', '', $formula_array[$i]);
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();
$earnings_result_array = array_reduce($earn_trans_result, function($result, $arr){
$result[$arr['transactions_month']][$arr['employee_code']] = $arr;
return $result;
}, array());
if((int)$process_mode === 3){
if($emp_rslt){
$this->process_worksheet($emp_rslt,$process_month,$annexure_sts_val,$sec10_result,$gross_other_income_result,$hra_result,$exist_tax_array,$section_array,$earnings_result_array,$trans_array,$column_rslt,$emp_array);
exit(0);
}else{
echo json_encode(array('success' => False,'msg'=> "Please try After Sometime..!"));
exit(0);
}
}
$final_line = "";
$tr_line = "";
$sec10_upd_query = "";
$table_data = "";
$tax_insert_values = "";
$tax_update_values = "";
$offline_tds = 0;
$pt_proj_amt = 0;
$cess_value = 0;
$tax_values = "";
foreach ($emp_rslt as $key => $emp){
$employee_code = $emp->employee_code;
$emp_name = $emp->emp_name;
$emp_role = $emp->role;
$category_name = $emp->category_name;
$metro = $emp->metro;
$emp_age = $emp->emp_age;
$designation = $emp->designation;
$tax_location = $emp->professional_tax_location;
//$income_tax_type = $emp->income_tax_type;
$date_of_joining = $emp->date_of_joining;
if($pt_projection_formula) {
$pt_proj_amt = $trans_array[$employee_code][$pt_projection_formula];
}
$income_tax_type = $section_dec_array[$employee_code]['income_tax_type'];
if(!$income_tax_type){
$income_tax_type = 1; //Default OLD Tax Regime will be taken
}
$emp_trans_array = $trans_array[$employee_code];
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;
/** Get PT Projection value END **/
/** Earning Part Start - BSK **/
$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 = "";
$margin_relief_line = "";
$surcharge_pct_line = "";
$refund_line = "";
$relief_line = "";
$i = 0;
$earning_data = $this->get_earnings_data($process_month,$employee_code,$emp_role,$earnings_columns,$proj_combine,$gross_array,$actual_months,$process_date,$formula_array,$trans_array,$pf_match_formula,$gross_other_income_result,$fin_set_id,$proof_wise_val,$section_dec_array,$professional_tax_db,$earnings_result_array);
$remain_count = $earning_data['count'];
$pt_actual = $earning_data['pt_actual'];
$pf_actual = $earning_data['pf_actual'];
$pf_proj = $earning_data['pf_proj'];
$gross_total = $earning_data['gross_total'];
$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($emp_trans_array,$employee_code,$income_tax_type,$section_array,$section_dec_array,$pt_proj_amt, $tax_location,$remain_count,$professional_tax_db,$earned_array,$proof_wise_val,$process_month,$pf_proj_formula,$gross_total,$pt_actual,$pf_actual,$pf_proj,$other_income_result,$other_income_array);
$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, '.', '');
if ((int)$income_tax_type === 1){
$with_declaration_data = $this->with_declaration($employee_code,$emp_age,$gross_total);
$total_tax = $with_declaration_data['total_tax'];
$tax_insert_key .= $with_declaration_data['tax_insert_key'];
$tax_insert_value .= $with_declaration_data['tax_insert_value'];
$tax_update_qry .= $with_declaration_data['tax_update_qry'];
}else{ // Old Tax End
$without_declaration_data = $this->without_declaration($employee_code,$emp_age,$gross_total);
$total_tax = $without_declaration_data['total_tax'];
$tax_insert_key .= $without_declaration_data['tax_insert_key'];
$tax_insert_value .= $without_declaration_data['tax_insert_value'];
$tax_update_qry .= $without_declaration_data['tax_update_qry'];
} // New Tax End
$margin_relief = $this->margin_relief_amt($gross_total, $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) {
$tax_insert_key .= ',surcharge_pct' . ',surcharge_charge';
$tax_insert_value .= ',"' . $surcharge_pct . '"' . ',"' . $surcharge_charge . '"';
$tax_update_qry .= ',surcharge_pct = VALUES(surcharge_pct)' . ',surcharge_charge = VALUES(surcharge_charge)';
if ((int) $margin_relief_amt !== 0) {
//insert query collecting
$tax_insert_key .= ',margin_relief';
$tax_insert_value .= ',"' . $margin_relief_amt . '"';
$tax_update_qry .= ',margin_relief = VALUES(margin_relief)';
}
}
}
/** TAX SLAB Part END - BSK **/
//Less:87A of the Income Tax Act, 1961 rebate Tax refund calculation
$refund_sts = 0;
$rebate_amt = 0;
if ((int) $gross_total <= 500000){
$refund_sts = 1;
$rebate_amt = $total_tax;
//insert query collecting
}
$tax_insert_key .= ',rebate_amt';
$tax_insert_value .= ',"' . $rebate_amt . '"';
//updated query collecting
$tax_update_qry .= ',rebate_amt = VALUES(rebate_amt)';
//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, '.', '');
}
//with education cess metro_percentage
$cess_tax = $total_tax + $cess_value - $margin_relief_amt;
$cess_tax = number_format((float) $cess_tax, 2, '.', '');
//insert query collecting
$tax_insert_key .= ',tax_amt';
$tax_insert_value .= ',"' . $cess_tax . '"';
$tax_update_qry .= ',tax_amt = VALUES(tax_amt)';
}
$month_tds_val = "NIL";
$final_pay_tax = 0;
//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));
$date = new DateTime("01-$process_month 00:00:00");
$date->modify('-1 month');
$end_pre_month = $date->format("Y-m-d");
$end_pre_text = date("d-m-Y", strtotime($end_date));
$prev_tax = 0;
$prev_tax_qry = 'select IFNULL((sum(tds)),0) as tax_deducted,IFNULL((sum(monthly_tds)),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();
$prev_tax = $prev_tax_result[0]->tax_deducted;
$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 = $prev_tax + $monthly_tax + $offline_tds+$prev_company_tds;
//insert query collecting
$tax_insert_key .= ',tax_deducted';
$tax_insert_value .= ',"' . $prev_tax . '"';
//updated query collecting
$tax_update_qry .= ',tax_deducted = VALUES(tax_deducted)';
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, '.', '');
}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 .= ',final_tax_pay'.',final_tax_amt';
$tax_insert_value .= ',"' . $final_tax_pay_month . '","' . $month_tds_val . '"';
//updated query collecting
$tax_update_qry .= ',final_tax_pay = VALUES(final_tax_pay),final_tax_amt = VALUES(final_tax_amt)';
//Total Tax To Be Paid This Month
$exist_rows = $exist_tax_array[$employee_code]['count_val'];
$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 . '"),';
}
$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')");
$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 cw_tax_calculation(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 cw_tax_calculation(' . $tax_insert_key . ',trans_created_by,trans_created_date,edu_cess,income_tax_type) VALUES '.$tax_insert_values;
//echo $tax_bulk_upd_qry; die;
$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){
echo json_encode(array('success' => TRUE,'table_data' => $table_data));
}else{
echo json_encode(array('success' => FALSE,'msg' => "Please Try after sometime..."));
}
}
public function view_tax(){
$employee_code = $this->input->post('employee_code');
$process_month = $this->input->post('process_month');
$month_ar = "01-".$process_month;
$fin_year = date("m",strtotime($month_ar)) >= 4 ? date("Y",strtotime($month_ar)). '-' . (date("Y",strtotime($month_ar))+1) : (date("Y",strtotime($month_ar)) - 1). '-' . date("Y",strtotime($month_ar));
$fin_year = explode('-', $fin_year);
$start_month = $fin_year[0]."-04";
$end_month = $fin_year[1]."-03";
$view_tax_qry = 'select process_month,tds,monthly_tds from cw_transactions where employee_code like "' . $employee_code . '" and trans_status = 1 and DATE_FORMAT(str_to_date(CONCAT("01-", process_month), "%d-%m-%Y"), "%Y-%m") >= "'.$start_month.'" and DATE_FORMAT(str_to_date(CONCAT("01-", process_month), "%d-%m-%Y"), "%Y-%m") <= "'.$end_month.'" order by DATE_FORMAT(str_to_date(CONCAT("01-", process_month), "%d-%m-%Y"), "%Y-%m") desc';
$view_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$view_tax_qry')");
$view_tax_result= $view_tax_data->result_array();
$view_tax_data->next_result();
$tr_line = "";
foreach ($view_tax_result as $value) {
$process_month = $value['process_month'];
$tds = $value['tds'];
$month_tds = $value['monthly_tds'];
$tr_line .= "<tr><td>$process_month</td><td>$tds</td><td>$month_tds</td></tr>";
}
$view_table_data = "<table class='table table-striped table-hover' id='view_table_info'>
<thead>
<th>Process Month</th><th>Tds</th><th>Monthly Tds</th>
</thead>
<tbody>$tr_line</tbody>
</table>";
echo json_encode(array('success' => TRUE,'view_table_data' => $view_table_data));
}
public function generate_pdf(){
$employee_code = $this->input->post('code');
$content = $this->input->post('html');
$process_month = $this->input->post('process_month');
$cat_name = $this->input->post('category');
$password = $this->input->post('password_column');
// Load HTML content
$this->dompdf->loadHtml($content);
// (Optional) Setup the paper size and orientation
$this->dompdf->setPaper('A3', 'portrait');
// Render the HTML as PDF
$this->dompdf->render();
if($password){
define('DOMPDF_ENABLE_AUTOLOAD', false);
$this->dompdf->get_canvas()->get_cpdf()->setEncryption($password, $password);
}
// Output the generated PDF (1 = download and 0 = preview)
$output = $this->dompdf->output();
$folder = "worksheet/". $cat_name ."/" . $process_month;
//Check Folder Exist
if (!file_exists($folder)) {
mkdir($folder, 0777, true);
}
//Check File Exist
if (file_exists($folder . "/" . $employee_code . ".pdf")) {
unlink($folder . "/" . $employee_code . ".pdf");
}
file_put_contents($folder . "/" . $employee_code . ".pdf", $output);
echo $folder . "/" . $employee_code . ".pdf";
}
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){
$financial_info = $this->get_financial_year();
$end_date = $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
);
}
$tax_insert_key .= ',surcharge_pct' . ',surcharge_charge' . ',margin_relief';
$tax_insert_value .= ',"' . $surcharge_pct . '"' . ',"' . $inc_surcharge_tax . '"' . ',"' . $margin_relief . '"';
$tax_update_qry .= ',surcharge_pct = VALUES(surcharge_pct),surcharge_charge = VALUES(surcharge_charge),margin_relief = VALUES(margin_relief)';
return $margin_relief_arr;
}
public function get_pt_projection($pt_proj_amt, $tax_location, $process_month, $count, $employee_code, $professional_tax_db){
$ptax_qry = 'select cw_professional_tax.prime_professional_tax_id,location,calculation_period,ptax_deduction_month_first as first_period,ptax_deduction_month_second as second_period, osm_first_end,osm_second_end from cw_professional_tax_tax_range inner join cw_professional_tax on cw_professional_tax.prime_professional_tax_id = cw_professional_tax_tax_range.prime_professional_tax_id where cw_professional_tax.trans_status = 1 and location =' . $tax_location;
$ptax_data = $this->db->query("CALL sp_a_run ('SELECT','$ptax_qry')");
$ptax_result = $ptax_data->result();
$ptax_data->next_result();
$prof_tax_id = $ptax_result[0]->prime_professional_tax_id;
$location = $ptax_result[0]->location;
$calculation_mode = $ptax_result[0]->calculation_period;
$ptax_first_period = $ptax_result[0]->osm_first_end;
$ptax_second_period = $ptax_result[0]->osm_second_end;
$ptax_deduct_first = $ptax_result[0]->first_period;
$ptax_deduct_second = $ptax_result[0]->second_period;
$trans_month = $process_month;
$process_month = explode("-", $process_month);
$process_month_val = $process_month[0];
$process_year_val = $process_month[1];
$process_month_date = $process_month[1] . "-" . $process_month[0] . "-01";
//tax area Tamil Nadu tax calculation
//By Formula every month calculated ptax amount based on earn gross
$prof_tax = 0;
if ((int) $calculation_mode === 1) {
//By Formula -- First Method
$every_month_qry = 'select professional_tax_amount as ptax_amt from cw_professional_tax_tax_range where trans_status=1 and prime_professional_tax_id ="' . $prof_tax_id . '" and earning_range_from <= "' . floor($pt_proj_amt) . '" and earning_range_to >= ' . floor($pt_proj_amt);
$every_month_data = $this->db->query("CALL sp_a_run ('SELECT','$every_month_qry')");
$every_month_result = $every_month_data->result();
$every_month_data->next_result();
$prof_tax = ($every_month_result[0]->ptax_amt) * $count;
} else if ((int) $calculation_mode === 2) {
//By Monthly -- Second Method -- values
//create first and second tax pay period array
$period_qry = 'select osm_first_start,osm_first_end,osm_second_start,osm_second_end from cw_professional_tax where trans_status =1 and location ="' . $tax_location . '"';
$period_data = $this->db->query("CALL sp_a_run ('SELECT','$period_qry')");
$period_result = $period_data->result();
$period_data->next_result();
$osm_first_st_val = $period_result[0]->osm_first_start;
$osm_first_ed_val = $period_result[0]->osm_first_end;
$osm_second_st_val = $period_result[0]->osm_second_start;
$osm_second_ed_val = $period_result[0]->osm_second_end;
$first_period = array();
for ($osm_first_st_val; $osm_first_st_val <= $osm_first_ed_val; $osm_first_st_val++) {
$first_period[] = $osm_first_st_val;
}
$second_period_part_i = array();
for ($osm_second_st_val; $osm_second_st_val <= 12; $osm_second_st_val++) {
$second_period_part_i[] = $osm_second_st_val;
}
$second_period_part_ii = array();
for ($osm_second_ed_val; $osm_second_ed_val >= 1; $osm_second_ed_val--) {
$second_period_part_ii[] = $osm_second_ed_val;
}
sort($second_period_part_ii);
$second_period = array_merge($second_period_part_i, $second_period_part_ii);
$loop_count = 5;
$tot_count = 0;
//calculating pending projection count loop
if (in_array($process_month_val, $first_period)) {
$cur_count = array_search($process_month_val, array_values($first_period));
$tot_count = (int) $loop_count - (int) $cur_count;
} else if (in_array($process_month_val, $second_period)) {
$cur_count = array_search($process_month_val, array_values($second_period));
$tot_count = (int) $loop_count - (int) $cur_count;
}
//projection amount
$projection_total = (int) $pt_proj_amt * (int) $tot_count; //2 month count
//Find last total earned gross amount
$last_month_date = date("Y-m-d", strtotime(date("Y-m-d", strtotime($process_month_date)) . " - " . $cur_count . " months"));
$previous_tax_qry = 'select ifnull(sum(total_earnings),0) as total_earnings from cw_transactions where trans_status =1 and employee_code = "' . $employee_code . '" and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") BETWEEN date_format("' . $last_month_date . '", "%Y-%m") and date_format("' . $process_month_date . '", "%Y-%m")';
$previous_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$previous_tax_qry')");
$previous_tax_result = $previous_tax_data->result();
$previous_tax_data->next_result();
$previous_earned = $previous_tax_result[0]->total_earnings; //upto this month sum earned gross
$total_earn = (int) $previous_earned + (int) $projection_total;
//loop processing count
$process_count = (int) $tot_count;
//Find current professional tax pay amount
$find_tax_qry = 'select professional_tax_amount as ptax_amt from cw_professional_tax_tax_range where trans_status = 1 and prime_professional_tax_id ="' . $prof_tax_id . '" and earning_range_from <= "' . floor($total_earn) . '" and earning_range_to >= ' . floor($total_earn);
$find_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$find_tax_qry')");
$find_tax_result = $find_tax_data->result();
$find_tax_data->next_result();
$find_tax_val = $find_tax_result[0]->ptax_amt;
//Find last paying professional tax amount and find final professional tax values and return to ptax column
$last_tax_qry = 'select ifnull(sum(' . $professional_tax_db . '),0) as professional_tax from cw_transactions where trans_status =1 and employee_code = "' . $employee_code . '" and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") BETWEEN date_format("' . $last_month_date . '", "%Y-%m") and date_format("' . $process_month_date . '", "%Y-%m")';
$last_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$last_tax_qry')");
$last_tax_result = $last_tax_data->result();
$last_tax_data->next_result();
$last_tax_val = $last_tax_result[0]->professional_tax;
$prof_tax_amt = $find_tax_val - $last_tax_val;
if ($count > 6) {
$pending_tax_val = $find_tax_result[0]->ptax_amt;
$prof_tax = $pending_tax_val + $prof_tax_amt;
} else {
$prof_tax = ($prof_tax_amt / $process_count) * $count;
}
} else if ((int) $calculation_mode === 3) {
//By Once in six month -- Last Method
if (((int) $process_month_val === (int) $ptax_deduct_first) || ((int) $process_month_val === (int) $ptax_deduct_second)) {
$period_qry = 'select osm_first_start,osm_first_end,osm_second_start,osm_second_end from cw_professional_tax where trans_status =1 and location ="' . $tax_location . '" ';
$period_data = $this->db->query("CALL sp_a_run ('SELECT','$period_qry')");
$period_result = $period_data->result();
$period_data->next_result();
$osm_first_st_val = $period_result[0]->osm_first_start;
$osm_first_ed_val = $period_result[0]->osm_first_end;
$osm_second_st_val = $period_result[0]->osm_second_start;
$osm_second_ed_val = $period_result[0]->osm_second_end;
$first_period = array();
for ($osm_first_st_val; $osm_first_st_val <= $osm_first_ed_val; $osm_first_st_val++) {
$first_period[] = $osm_first_st_val;
}
$second_period_part_i = array();
for ($osm_second_st_val; $osm_second_st_val <= 12; $osm_second_st_val++) {
$second_period_part_i[] = $osm_second_st_val;
}
$second_period_part_ii = array();
for ($osm_second_ed_val; $osm_second_ed_val >= 1; $osm_second_ed_val--) {
$second_period_part_ii[] = $osm_second_ed_val;
}
sort($second_period_part_ii);
$second_period = array_merge($second_period_part_i, $second_period_part_ii);
$loop_count = 5;
$tot_count = 0;
//calculating pending projection count loop
if (in_array($process_month_val, $first_period)) {
$cur_count = array_search($process_month_val, array_values($first_period));
$tot_count = (int) $loop_count - (int) $cur_count;
} else if (in_array($process_month_val, $second_period)) {
$cur_count = array_search($process_month_val, array_values($second_period));
$tot_count = (int) $loop_count - (int) $cur_count;
}
//Find Projection amount
//projection amount
$projection_total = (int) $pt_proj * (int) $tot_count; //2 month count
//calculate sum of actual and projection amount and six month amount value
$last_month_date = date("Y-m-d", strtotime(date("Y-m-d", strtotime($process_month_date)) . " - " . $cur_count . " months"));
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")';
}
$previous_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$previous_tax_qry')");
$previous_tax_result = $previous_tax_data->result();
$previous_tax_data->next_result();
$previous_earned = $previous_tax_result[0]->total_earnings; //find curr to pre earned gross sum value
$total_earn = (int) $previous_earned + (int) $projection_total; // total sum (earned + project)
//Find current professional tax pay amount
$find_tax_qry = 'select professional_tax_amount as ptax_amt from cw_professional_tax_tax_range where trans_status=1 and prime_professional_tax_id ="' . $prof_tax_id . '" and earning_range_from <= "' . floor($total_earn) . '" and earning_range_to >= ' . floor($total_earn);
$find_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$find_tax_qry')");
$find_tax_result = $find_tax_data->result();
$find_tax_data->next_result();
$prof_tax = $find_tax_result[0]->ptax_amt;
}
}
return round($prof_tax);
}
public function get_pf_projection($emp_trans_array,$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@";
$for_value = $emp_trans_array[$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)
{
$financial_info = $this->get_financial_year();
$start_date = $financial_info[0]->start_date;
$end_date = $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>";
$company_name = $this->company_info[0]->company_name;
$company_address = $this->company_info[0]->address;
$company_city = $this->company_info[0]->city;
$company_state = $this->company_info[0]->state;
$company_country = $this->company_info[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='page-break-after: always;width:auto; 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>
$month_td_list
$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();
$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) {
if($check_form === "fixed_gross"){
$value = "\$trans['" . $check_form . "']";
}else{
$value = "\$trans_array[\$key]['" . $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 = str_replace("~", '"', $formula_code);
$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 }?>";
$oldmask = umask(0);
fopen("$dynamic_file_name", "w");
file_put_contents("$dynamic_file_name", $code);
chmod($dynamic_file_name, 0777);
umask($oldmask);
require_once("$dynamic_file_name");
$final_result_array = tax($trans_array);
unlink("$dynamic_file_name");
return $final_result_array;
}
//BSK START on 22July2020
public function get_earnings_data($process_month,$employee_code,$emp_role,$earnings_columns,$proj_combine,$gross_array,$actual_months,$process_date,$formula_array,$trans_array,$pf_match_formula,$gross_other_income_result,$fin_set_id,$proof_wise_val,$section_dec_array,$professional_tax_db,$earnings_result_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;
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";
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
if ($projection_column) {
$proj = $trans_array[$employee_code][$projection_column];
}else{
$proj = 0;
}
$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
$pt_actual = $pt_actual + $pt_value;
$pf_actual = $pf_actual + $pf_value;
$pf_proj = $pf_value;
}else{ //For Projection
$count++;
}
}
$total_proj = ($proj) * ($count); //For Projection calc
$total = $total_actual + $total_proj; //total amount
$total_proj = number_format((float) $total_proj, 2, '.', '');
$total = number_format((float) $total, 2, '.', '');
$total_earn = $total_earn + $total;
if ($gross_section === "1") {
$gross_1 = $gross_1 + $total;
} 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;
$income_description = $gross_value->income_description;
$other_income_column = $gross_value->other_income_column;
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($other_income_val > 0){
$total_earn = $total_earn + $other_income_val;
}
}
$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,"pf_proj"=>$pf_proj,"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;
$sec10_upd_query = "";
$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;
}
$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];
$dec_value = $section_dec_array[$employee_code][$tax_subsection_column];
if (!$dec_value) {
$dec_value = "0.00";
}
$house_rent = "0.00";
$rent_received = "0.00";
//HRA CALCUALTIONS FOR ALL
if ($tax_subsection_column === 'taxsubsec_1'){
$earned_hra = $earned_array[$earnings];
$house_rent = $section_dec_array[$employee_code]["tax_house_rent"];
//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;
//If Employee joined inbetween
if(($date_of_joining >= $start_date) && ($date_of_joining <= $end_date)){
//Get No of Days for HRA
$datediff = strtotime($end_date) - strtotime($date_of_joining);
$worked_days = round($datediff / (60 * 60 * 24));
$house_rent = round(($house_rent/365)*($worked_days+1));
}
$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;
}
}
if((int)$income_tax_type === 2){
$rent = 0;
$metro_rslt = 0;
}
//insert query collecting
$tax_insert_key .= ",rent,actual_rent_pct,actual_rent,metro_rslt,metro_pct";
$tax_insert_value .= ',"' . $rent . '"'.',"' . $actual_rent_pct . '"'.',"' . $actual_rent . '"'.',"' . $metro_rslt . '"'.',"' . $metro_pct . '"';
$tax_update_qry .= ',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($earned_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($emp_trans_array,$employee_code,$income_tax_type,$section_array,$section_dec_array,$pt_proj_amt, $tax_location,$count,$professional_tax_db,$earned_array,$proof_wise_val,$process_month,$pf_proj_formula,$gross_total,$pt_actual,$pf_actual,$pf_proj,$other_income_result,$other_income_array){
/** 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
//$dec_value = $section_dec_result[0]->$tax_subsection_column;
$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") {
$dec_value = $tax_subsection_limit;
$total_sub = $total_sub + $tax_subsection_limit;
} 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;
$pt_total = number_format((float) $pt_total, 2, '.', '');
$total_sub = $total_sub + $pt_total;
$total_sub = number_format((float) $total_sub, 2, '.', '');
$dec_value = $pt_total;
if((int)$income_tax_type === 2){
$pt_proj = 0;
$pt_actual = 0;
$pt_total = 0;
}
//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_proj = $pf_proj*$count;
/** 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, '.', '');
$dec_value = $pf_total;
if((int)$income_tax_type === 2){
$pf_proj = 0;
$pf_actual = 0;
$pf_total = 0;
$match_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_proj),pf_total = VALUES(pf_proj),section_matching = VALUES(pf_proj)';
}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 .= ',"' . $dec_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_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;
$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;
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(!$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 = $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 = 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 with_declaration($employee_code,$emp_age,$gross_total){
$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_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();
$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;
} 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, '.', '');
}
//insert query collecting
$tax_insert_key .= ',slab_type,total_tax,total_tax_on_income';
$tax_insert_value .= ',"' . $slap_type . '","' . $final_value . '","' . $total_tax . '"';
//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)';
return array("total_tax"=>$total_tax,"tax_insert_key"=>$tax_insert_key,"tax_insert_value"=>$tax_insert_value,"tax_update_qry"=>$tax_update_qry);
}
public function without_declaration($employee_code,$emp_age,$gross_total){
/** 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,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 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;
} else {
$final_tax = 0;
}
} 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 = ($tax_range_start * $tax_amount) / 100;
$final_tax = $final_tax - $tax_range_start;
} else if ((int) $final_tax > 0 && $final_value >= $tax_range_start && $final_value <= $tax_range_end) {
$tax_range_start = $tax_range_end - $tax_range_start;
$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, '.', '');
}
//insert query collecting
$tax_insert_key .= ',slab_type,total_tax,total_tax_on_income';
$tax_insert_value .= ',"' . $slap_type . '","' . $final_value . '","' . $total_tax . '"';
//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)';
return array("total_tax"=>$total_tax,"tax_insert_key"=>$tax_insert_key,"tax_insert_value"=>$tax_insert_value,"tax_update_qry"=>$tax_update_qry);
}
public function process_worksheet($emp_rslt,$process_month,$annexure_sts_val,$sec10_result,$gross_other_income_result,$hra_result,$exist_tax_array,$section_array,$earnings_result_array,$trans_array,$column_rslt,$emp_array){
require_once "./dist/eos-1.0.0/eos.class.php";
$eq = new eqEOS();
$pdf_data = array();
$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;
$process_sheet_query = 'SELECT * FROM `cw_tax_calculation` WHERE process_month = "' . $process_month . '" and fin_set_id = "'.$fin_set_id.'" and cw_tax_calculation.trans_status = 1';
$process_sheet_data = $this->db->query("CALL sp_a_run ('SELECT','$process_sheet_query')");
$process_sheet_result = $process_sheet_data->result_array();
$process_sheet_data->next_result();
$process_sheet_array = array_reduce($process_sheet_result, function ($result, $arr) {
$result[$arr['process_month']][$arr['emp_code']] = $arr;
return $result;
}, array());
if(empty($process_sheet_array)){
echo json_encode(array(
'success' => false,
'msg' => "No data Available.."
));
exit(0);
}
//Validation Checking
//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_name = $this->company_info[0]->company_name;
$company_address = $this->company_info[0]->address;
$company_city = $this->company_info[0]->city;
$company_state = $this->company_info[0]->state;
$company_country = $this->company_info[0]->country;
$pwd_column = $this->company_info[0]->password_column;
$column_rslt_arr = json_decode(json_encode($column_rslt),true);
$emp_field_type = array_reduce($column_rslt_arr, function ($result, $arr) {
$result[$arr['label_name']] = $arr['field_type'];
return $result;
}, array());
//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();
$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;
//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);
$final_line = "";
$tr_line = "";
$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;
$category_name = $emp->category_name;
$metro = $emp->metro;
$emp_age = $emp->emp_age;
$designation = $emp->designation;
$tax_location = $emp->professional_tax_location;
// $income_tax_type = $emp->income_tax_type;
$income_tax_type = $process_sheet_array[$process_month][$employee_code]['income_tax_type'];
//$income_tax_type = 1;
if ((int) $emp_age === 0){
$emp_age = 30; //default general people
}
/** Earning Part Start - BSK **/
$tr_line = "";
$earned_array = array();
$annexure_data = array();
$act_earn_pdf = array();
$total_proj = 0;
$gross_total = 0;
$total_earn = 0;
$pt_total = 0;
$pf_total = 0;
$final_tax_pay_month = 0;
$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];
$earn_column = "earn_$value";
$proj_column = "proj_$value";
if((int)$annexure_sts_val === 1){
foreach ($actual_months as $actual){
$month = $actual->format("m-Y");
$month_date = $actual->format("Y-m-d");
$month_date = $actual->format("Y-m-d");
if(strtotime($month_date) <= strtotime($process_date)) { //For Actual
$annexure_data[$value][$month] = $earnings_result_array[$month][$employee_code][$value];
}else{
if($projection_column){
$annexure_data[$value][$month] = $trans_array[$employee_code][$projection_column];
}
}
}
}
$total_actual = $process_sheet_array[$process_month][$employee_code][$earn_column];
$total_proj = $process_sheet_array[$process_month][$employee_code][$proj_column];
$total = $total_actual + $total_proj; //total amount
$total_earn = $total_earn + $total;
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++;
$earned_array[$value] = $total;
}
$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_val = $process_sheet_array[$employee_code][$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;
}
}
$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 **/
if ((int) $income_tax_type === 1){
/** SECTION 10 Part START - BSK **/
$dec_value = 0;
$sec10_line = "";
$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];
//HRA CALCUALTIONS FOR ALL
if ($tax_subsection_column === 'taxsubsec_1'){
$earned_hra = $earned_array[$earnings];
$rent = $process_sheet_array[$process_month][$employee_code]["rent"];
$sec10_least_value = $process_sheet_array[$process_month][$employee_code]["hra"];
$metro_rslt = $process_sheet_array[$process_month][$employee_code]["metro_rslt"];
$metro_pct = $process_sheet_array[$process_month][$employee_code]["metro_pct"];
$actual_rent_pct = $process_sheet_array[$process_month][$employee_code]["actual_rent_pct"];
$sec10_line .= "<tr><td> $tax_subsection_name</td><td></td><td></td><td>$rent</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{
$dec_value = $process_sheet_array[$process_month][$employee_code][$tax_subsection_column];
$sec10_line .= "<tr><td> $tax_subsection_name</td><td>$dec_value</td><td>$tax_subsection_limit</td><td>$dec_value</td></tr>";
}
}
$total_sec10 = 'tot_' . $tax_section_column;
$total_sec10 = $process_sheet_array[$process_month][$employee_code][$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_tot_' . $tax_section_column;
$gross_total = $process_sheet_array[$process_month][$employee_code][$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 = "";
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
//THIS DEC VALUE PRODUCE A DECLAR AMOUNT NOT LIMIT
$dec_value = $process_sheet_array[$process_month][$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);
}
$match_value = $process_sheet_array[$process_month][$employee_code]['section_matching'];
if ($tax_subsection_column === "taxsubsec_8") {
$sub_section_line .= "<tr><td> $tax_subsection_name</td><td>$dec_value</td><td></td><td>$tax_subsection_limit</td></tr>";
}else if ($tax_subsection_column === "taxsubsec_10"){
/** Get PT Projection value START **/
$pt_proj = $process_sheet_array[$process_month][$employee_code]['pt_proj'];
$pt_actual = $process_sheet_array[$process_month][$employee_code]['pt_actual'];
/** Get PT Projection value END **/
$pt_total = $process_sheet_array[$process_month][$employee_code]['pt_total'];
if ($pt_total > 0) {
$sub_section_line .= "<tr><td> $tax_subsection_name</td><td></td><td></td><td>$pt_total</td></tr>";
}
}else
if ($tax_subsection_column === "taxsubsec_11") {
/** Get PF Projection value START **/
$pf_proj = $process_sheet_array[$process_month][$employee_code]['pf_proj'];
/** Get PF Projection value END **/
$pf_actual = $process_sheet_array[$process_month][$employee_code]['pf_actual'];
$pf_total = $process_sheet_array[$process_month][$employee_code]['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>$dec_value</td></tr>";
//}
} else {
if($match_value) {
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{
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, '.', '');
}
$total_sub = 'tot_' . $tax_section_column;
$total_sub = $process_sheet_array[$process_month][$employee_code][$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_tot_' . $tax_section_column;
$gross_total = $process_sheet_array[$process_month][$employee_code][$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>";
if($tax_section_column === "taxsec_2"){
// 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;
$income_val = $process_sheet_array[$process_month][$employee_code][$other_income_column];
if(!$income_val){
$income_val = 0.00;
}
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, '.', '');
$sub_section_line .= $add_income_line;
$sub_section_line .= $less_income_line;
$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>";
}
}
//echo "BSK $key :: $tax_section_column :: $gross_total <br/>";
}else{
// 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;
$income_val = $process_sheet_array[$process_month][$employee_code][$other_income_column];
if(!$income_val){
$income_val = 0.00;
}
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, '.', '');
$sub_section_line .= $add_income_line;
$sub_section_line .= $less_income_line;
$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>";
}
$gross_total = ceil($gross_total / 10) * 10; //10 Rupee Rounding
$final_value = number_format((float) $gross_total, 2, '.', '');
$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 === 1) {
/** 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 = round(($tax_range_start * $tax_amount) / 100);
$tax_value = number_format((float) $tax_value, 2, '.', '');
$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 = round(($final_tax * $tax_amount) / 100);
$tax_value = number_format((float) $tax_value, 2, '.', '');
$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>";
} else { // Old Tax End
/** 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,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 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();
$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;
$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_start = $tax_range_end - $tax_range_start;
$tax_value = round(($tax_range_start * $tax_amount) / 100);
$tax_value = number_format((float) $tax_value, 2, '.', '');
$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 = round(($final_tax * $tax_amount) / 100);
$tax_value = number_format((float) $tax_value, 2, '.', '');
$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>";
} // New Tax End
$margin_relief = $process_sheet_array[$process_month][$employee_code]['margin_relief'];
if (!empty($margin_relief)) {
$surcharge_pct = $process_sheet_array[$process_month][$employee_code]['surcharge_pct'];
$surcharge_charge = $process_sheet_array[$process_month][$employee_code]['surcharge_charge'];
if ((int) $surcharge_pct !== 0) {
$surcharge_pct_line = "<tr><td> Surcharge Add ($surcharge_pct %)</td><td></td><td></td><td>$surcharge_charge</td></tr>";
$margin_relief_amt = $process_sheet_array[$process_month][$employee_code]['margin_relief'];
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>";
}
}
}
/** 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;
}
//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, '.', '');
}
$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>";
}
$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));
$date = new DateTime("01-$process_month 00:00:00");
$date->modify('-1 month');
$end_pre_month = $date->format("Y-m-d");
$end_pre_text = date("d-m-Y", strtotime($end_date));
$prev_tax = 0;
$prev_tax = $process_sheet_array[$process_month][$employee_code]['tax_deducted'];
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>";
}
$final_tax_pay_month = $process_sheet_array[$process_month][$employee_code]['final_tax_pay'];
$tax_pay_line = "<tr><td>Tax Payable / Refundable</td><td></td><td></td><td>$final_tax_pay_month</td></tr>";
$month_tds_val = $process_sheet_array[$process_month][$employee_code]['final_tax_amt'];
//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>";
$password_arr = explode(',', $pwd_column);
$password_col = "";
foreach ($password_arr as $pwd_key => $pwd_value) {
$pwd_field_type = $emp_field_type[$pwd_value];
if((int)$pwd_field_type === 4){
$pass_val = $emp_array[$employee_code][$pwd_value];
$password_col .= date('d-m-Y',strtotime($pass_val));
}else{
$password_col .= $emp_array[$employee_code][$pwd_value];
}
}
$password_col = preg_replace('/[^a-zA-Z0-9]/s','',$password_col);
if(!$password_col){
$password_col = "";
}
$table_data = "<div style='page-break-after: always;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-" . $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
$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>";
$annexure_tbl_data = "";
if((int)$annexure_sts_val === 1){
// do later
$annexure_tbl_data = $this->get_yearly_annex($annexure_data,$employee_code,$view_det,$emp_name,$designation);
$annexure_tbl_data = "\n".$annexure_tbl_data;
}
$table_data = "<!DOCTYPE html><html> <body>" . $table_data .$annexure_tbl_data. "</body></html>";
$pdf_data[$employee_code]['table_data'] = $table_data;
$pdf_data[$employee_code]['password_column'] = $password_col;
$category = $category_name;
}
$count = count($pdf_data);
echo json_encode(array(
'success' => TRUE,
'pdf_data' => $pdf_data,
'category' => $category,
'msg' => "process work sheet generated",
'count' => $count
));
}
}
?>