File: /home/cafsindia/uds.cafsinfotech.in/Dump/ZRM/application/controllers/Pro_tax_process.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Pro_tax_process extends Action_controller{
public function __construct(){
parent::__construct('pro_tax_process');
$this->load->model('income_tax_process_model');
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');
}
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 and prime_category_id !=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 . '%" or emp_name 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 = (int) $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");
$process_month_dt = "01-".$process_month;
$process_from = "projection_tax_process";
$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)));
//Check Incometax lock exist
$exist_qry = 'select count(prime_income_tax_lock_id) as income_tax_count from cw_income_tax_lock where tax_month = "'.$process_month.'" and income_tax_status = 1 and trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$exist_data->next_result();
$num_rows = $exist_result[0]->income_tax_count;
if((int)$num_rows > 0){
echo json_encode(array('success' => false, 'msg' => 'Income Tax Locked for this Month'));
exit(0);
}
//Check payroll lock exist
$payroll_exist_qry = 'select count(prime_payroll_id) as payroll_count from cw_payroll where pay_month = "'.$process_month.'" and status = 1 and trans_status = 1';
$payroll_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$payroll_exist_result = $payroll_exist_data->result();
$payroll_exist_data->next_result();
$num_rows = $payroll_exist_result[0]->payroll_count;
if((int)$num_rows > 0){
echo json_encode(array('success' => false, 'msg' => 'Payroll Locked for this Month'));
exit(0);
}
$process_dt = new DateTime("01-$process_month 00:00:00");
$process_dt->modify('-1 month');
$next_month = $process_dt->format("Y-m-d");
$process_month_only = '01-'.$process_month;
$process_month_only = date('Y-m-d',strtotime($process_month_only));
$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 . '" ';
$trans_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 . '"';
$trans_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 payroll exist
$istrans_exist_qry = 'select count(*) as result_count from cw_transactions where trans_status = 1 and transactions_month = "'.$next_month.'" and fandf = 2'.$trans_query;
$istrans_data = $this->db->query("CALL sp_a_run ('SELECT','$istrans_exist_qry')");
$istrans_result = $istrans_data->result();
$istrans_data->next_result();
$istrans_count = (int)$istrans_result[0]->result_count;
if($istrans_count > 0){
echo json_encode(array('success' => false, 'msg' => "Payroll Processed for Next Month..!"));
exit(0);
}
//Check Previous Company TDS
$prev_comp_tds_qry = 'select IFNULL(previous_tax,0) as previous_tax,employee_code from cw_previous_company_income where financial_setting_id = "'.$fin_set_id.'" and trans_status = 1';
$prev_comp_tds_data = $this->db->query("CALL sp_a_run ('SELECT','$prev_comp_tds_qry')");
$prev_comp_tds_result = $prev_comp_tds_data->result_array();
$prev_comp_tds_data->next_result();
if($prev_comp_tds_result){
$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 SUM(amount) as 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,cw_employees.termination_status FROM cw_transactions as a inner join cw_employees on cw_employees.employee_code = a.employee_code left JOIN cw_position on cw_position.prime_position_id = cw_employees.designation left join cw_category on cw_category.prime_category_id = cw_employees.role INNER JOIN (SELECT employee_code, date_format(MAX(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_transactions WHERE date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") and termination_status=0 ' .$where_query. ' GROUP BY employee_code) as groupedtt ON a.employee_code = groupedtt.employee_code AND a.transactions_month = groupedtt.max_date';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_info_query')");
$emp_rslt = $emp_data->result();
$emp_data->next_result();
if(!$emp_rslt){
echo json_encode(array('success' => false, 'msg' => "No Data Available..!"));
exit(0);
}
$del_query = "";
if($process_mode === 1){
$rslt = $this->income_tax_process_model->income_tax_with_process_payroll($process_month,$annexure_sts,$process_type,$process_emp_code,$process_role,1,$proof_wise,$month_name,$logged_id,$today_date,$process_from);
return $rslt;
}else
if($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_proj_calculation_dlt SELECT *,"' . $this->logged_id . '" as trans_deleted_by,"'.$today_date.'" as trans_deleted_date FROM cw_tax_proj_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_proj_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_proj_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{
if($emp_rslt){
// $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);
// }
$istrans_exist_qry = 'select count(*) as result_count from cw_transactions where trans_status = 1 and transactions_month = "'.$process_month.'" and fandf = 2'.$trans_query;
$istrans_data = $this->db->query("CALL sp_a_run ('SELECT','$istrans_exist_qry')");
$istrans_result = $istrans_data->result();
$istrans_data->next_result();
$istrans_count = (int)$istrans_result[0]->result_count;
if($istrans_count > 0){
echo json_encode(array('success' => false, 'msg' => "Payroll Processed for this Month.. Please process the Income tax Process..!"));
exit(0);
}
//Validation Checking
//Section 10 Part
$sec10_query = 'SELECT tax_section_column,tax_subsection_column,tax_act_details,earnings,tax_subsection_limit,cw_tax_section.tax_section,bill_required,exemption_method,cw_tax_sub_section.income_tax_type from `cw_income_matching` inner join cw_tax_sub_section on cw_tax_sub_section.tax_subsection_column = cw_income_matching.excemption_component inner join cw_tax_section on cw_tax_section.prime_tax_section_id = cw_tax_sub_section.tax_section and cw_income_matching.financial_setting_id = '.$fin_set_id.' and cw_tax_sub_section.financial_setting_id = '.$fin_set_id.' where cw_income_matching.trans_status = 1 and cw_tax_sub_section.trans_status = 1 order by subsection_order';
$sec10_data = $this->db->query("CALL sp_a_run ('SELECT','$sec10_query')");
$sec10_result = $sec10_data->result();
$sec10_data->next_result();
if (!$sec10_result){
echo json_encode(array(
'success' => False,
'message' => "Section 10 Excemption Details not Mapped..!"
));
exit(0);
}
//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;
//Dynamic column list array
$get_column_qry = 'select * from cw_payroll_function_map where trans_status=1';
//map_statutory_name= 3 and
$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();
foreach ($get_column_result as $column_name){
$loc_name = $column_name->loc_name;
$db_column = $column_name->db_column;
if ($loc_name == "professional_tax_amount"){
$professional_tax_db = $db_column;
}else
if ($loc_name == "fixed_pf"){
$fixed_pf_db = $db_column;
}else
if ($loc_name == "pt_gross"){
$pt_gross_db = $db_column;
}
}
if($professional_tax_db === ''){
echo json_encode(array('success'=> False,'msg'=> "Ptax Column not Mapped.."));
exit(0);
}
if($fixed_pf_db === ''){
echo json_encode(array('success'=> False,'msg'=> "Fixed PF Column not Mapped.."));
exit(0);
}
$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 and financial_setting_id = '.$fin_set_id.' order by order_no';
$earnings_data = $this->db->query("CALL sp_a_run ('SELECT','$earnings_query')");
$earnings_result = $earnings_data->result();
$earnings_data->next_result();
//Gross Income Details //and cw_other_income.income_type in (3,4,5)
$gross_other_income_qry = 'select cw_income_type.income_type,income_description,other_income_column,annual_limit from cw_other_income inner join cw_income_type on cw_income_type.prime_income_type_id =cw_other_income.income_type where cw_other_income.trans_status = 1 and cw_other_income.income_type in (3,4,5) order by cw_income_type.income_type';
$gross_other_income_info = $this->db->query("CALL sp_a_run ('SELECT','$gross_other_income_qry')");
$gross_other_income_result = $gross_other_income_info->result();
$gross_other_income_info->next_result();
//House rent Allowance
$hra_query = 'SELECT formula,actual_rent_paid_percentage,metro_percentage,non_metro_percentage,education_cess_percentage FROM `cw_financial_setting` WHERE cw_financial_setting.trans_status = 1 and prime_financial_setting_id = "'.$fin_set_id.'"';
$hra_data = $this->db->query("CALL sp_a_run ('SELECT','$hra_query')");
$hra_result = $hra_data->result();
$hra_data->next_result();
if(empty($hra_result)){
echo json_encode(array(
'success' => false,
'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,cw_tax_sub_section.income_tax_type FROM `cw_tax_section` inner join cw_tax_sub_section on cw_tax_sub_section.tax_section = cw_tax_section.prime_tax_section_id WHERE cw_tax_section.tax_section_column != "taxsec_1" AND cw_tax_sub_section.trans_status = 1 and cw_tax_sub_section.financial_setting_id = '.$fin_set_id.' order by prime_tax_section_id,subsection_order ASC';
$section_data = $this->db->query("CALL sp_a_run ('SELECT','$section_query')");
$section_result = $section_data->result();
$section_data->next_result();
$section_array = array();
foreach ($section_result as $key => $value){
$section_array[$value->tax_section][] = array(
"tax_section_column" => $value->tax_section_column,
"prime_tax_sub_section_id" => $value->prime_tax_sub_section_id,
"tax_subsection_column" => $value->tax_subsection_column,
"tax_act_details" => $value->tax_act_details,
"tax_subsection_limit" => $value->tax_subsection_limit,
"bill_required" => $value->bill_required,
"exemption_method" => $value->exemption_method,
"section_limit" => $value->section_limit,
"income_tax_type" => $value->income_tax_type
);
}
//Projection Data
$projection_query = 'SELECT GROUP_CONCAT(earnings) as proj_earnings,GROUP_CONCAT(view_name) as proj_view_name,GROUP_CONCAT(matching_components) as projection FROM `cw_income_matching` INNER JOIN cw_form_setting on cw_form_setting.label_name = cw_income_matching.matching_components WHERE cw_income_matching.trans_status = 1 and financial_setting_id = '.$fin_set_id.' order by order_no asc';
$projection_data = $this->db->query("CALL sp_a_run ('SELECT','$projection_query')");
$projection_result = $projection_data->result();
$projection_data->next_result();
$earnings_columns = explode(",", $earnings_result[0]->earnings);
$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) and transaction_type = 2 ORDER BY prime_form_id';
$column_data = $this->db->query("CALL sp_a_run ('SELECT','$column_qry')");
$column_rslt = $column_data->result();
$column_data->next_result();
$columns = array_column($column_rslt, 'label_name');
$column_label = implode(',', $columns);
$emp_sal_query = 'SELECT a.employee_code,a.role,'.$column_label.' FROM cw_transactions as a INNER JOIN (SELECT employee_code, date_format(MAX(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_transactions WHERE date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") ' .$where_query. ' GROUP BY employee_code) as groupedtt ON a.employee_code = groupedtt.employee_code AND a.transactions_month = groupedtt.max_date';
$emp_sal_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_sal_query')");
$emp_sal_rslt = $emp_sal_data->result_array();
$emp_sal_data->next_result();
$trans_array = array();
foreach ($emp_sal_rslt as $key => $value){
$trans_array[$value['employee_code']] = $value;
}
$proj_qry = 'select payroll_formula,formula_for,out_column from cw_payroll_formula where out_column IN ("fpt_gross") and trans_status = 1 order by abs(formula_order),abs(formula_mode) asc';
$proj_data = $this->db->query("CALL sp_a_run ('SELECT','$proj_qry')");
$proj_result = $proj_data->result();
$proj_data->next_result();
$payroll_formulas = array();
foreach ($proj_result as $key => $value) {
$payroll_formulas[$value->formula_for][$value->out_column] = $value->payroll_formula;
}
$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,annual_limit from cw_other_income inner join cw_income_type on cw_income_type.prime_income_type_id =cw_other_income.income_type where cw_other_income.trans_status = 1 and (cw_other_income.income_type =1 or cw_other_income.income_type =2) order by cw_income_type.income_type';
$other_income_info = $this->db->query("CALL sp_a_run ('SELECT','$other_income_qry')");
$other_income_result = $other_income_info->result();
$other_income_info->next_result();
//Get Earnings Query
$pf_match = "";
$earn_qry = "employee_code,transactions_month";
foreach ($earnings_columns as $key => $value){
$formula = str_replace('@', '', $formula_array[$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;
}
}
//Check FandF input exist
$fandf_exist_qry = 'select employee_code from cw_fandf_monthly_input where process_month = "'.$process_month.'" and trans_status = 1';
$fandf_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$fandf_exist_qry')");
$fandf_exist_result = $fandf_exist_data->result();
$fandf_exist_data->next_result();
$fandf_array = array();
foreach ($fandf_exist_result as $key => $value){
$fandf_array[$value->employee_code] = $value->employee_code;
}
//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());
$proof_wise_val = 0;
if($proof_wise === "on"){
$proof_wise_val = 1;
}
$annexure_sts_val = 0;
if($annexure_sts === "on"){
$annexure_sts_val = 1;
}
$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);
}
}
}
public function get_months($start_date, $end_date){
$start = new DateTime($start_date);
$end = new DateTime($end_date);
$start->modify('first day of this month');
$end->modify('last day of this month');
$interval = DateInterval::createFromDateString('1 month');
$period = new DatePeriod($start, $interval, $end);
return $period;
}
public function month_count($process_date){
$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;
}
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_proj_calculation` WHERE process_month = "' . $process_month . '" and fin_set_id = "'.$fin_set_id.'" and cw_tax_proj_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,$end_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 order by order_no) as earnings,GROUP_CONCAT(formula order by order_no) as formula,GROUP_CONCAT(view_name order by order_no) as view_name,GROUP_CONCAT(gross_section) as gross_section FROM `cw_income_matching` INNER JOIN cw_form_setting on cw_form_setting.label_name = cw_income_matching.earnings WHERE cw_income_matching.trans_status = 1 and financial_setting_id = '.$fin_set_id.' order by order_no ASC';
$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 order by order_no) as proj_earnings,GROUP_CONCAT(view_name order by order_no) as proj_view_name,GROUP_CONCAT(matching_components order by order_no) as projection FROM `cw_income_matching` INNER JOIN cw_form_setting on cw_form_setting.label_name = cw_income_matching.matching_components WHERE cw_income_matching.trans_status = 1 and financial_setting_id = '.$fin_set_id.' order by order_no asc';
$projection_data = $this->db->query("CALL sp_a_run ('SELECT','$projection_query')");
$projection_result = $projection_data->result();
$projection_data->next_result();
$earnings_columns = explode(",", $earnings_result[0]->earnings);
$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;
$emp_status = (int)$emp->termination_status;
$income_tax_type = $process_sheet_array[$process_month][$employee_code]['income_tax_type'];
if((int)$income_tax_type === 1){
$rebate = $financial_info[0]->old_rebate;
$margin_relief = $financial_info[0]->old_m_relief;
}else{
$rebate = $financial_info[0]->new_rebate;
$margin_relief = $financial_info[0]->new_m_relief;
}
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 && $emp_status === 0){
$annexure_data[$value][$month] = $trans_array[$employee_code][$projection_column];
}
}
}
}
$total_actual = $process_sheet_array[$process_month][$employee_code][$earn_column];
if($emp_status === 0){ //If Inactive Emp
$total_proj = $process_sheet_array[$process_month][$employee_code][$proj_column];
}else{
$total_proj = 0;
}
$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[$process_month][$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;
$tax_type = explode(",",$value->income_tax_type);
//Check Data for get Whichever is Least
$earned_value = $earned_array[$earnings];
if(in_array($income_tax_type, $tax_type)){ // Based on OLD and NEW Regime
//HRA CALCUALTIONS FOR ALL
if ($tax_subsection_column === 'taxsubsec_1'){
$earned_hra = $earned_array[$earnings];
$rent = $process_sheet_array[$process_month][$employee_code]["rent"];
$house_rent_paid = $process_sheet_array[$process_month][$employee_code]["house_rent_paid"];
$sec10_least_value = $process_sheet_array[$process_month][$employee_code]["taxsubsec_1"];
$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"];
$actual_rent = $process_sheet_array[$process_month][$employee_code]["actual_rent"];
$sec10_line .= "<tr><td> $tax_subsection_name</td><td></td><td></td><td>$sec10_least_value</td></tr>";
$sec10_line .= "<tr><td> Calculations</td><td></td><td></td><td></td></tr>";
$sec10_line .= "<tr><td> A) $metro_pct% Basic </td><td>$metro_rslt</td><td></td><td></td></tr>";
$sec10_line .= "<tr><td> B) HRA Received </td><td>$earned_hra</td><td></td><td></td></tr>";
$sec10_line .= "<tr><td> C) Excess of Rent Paid Over $actual_rent_pct% Salary ($house_rent_paid-$actual_rent)</td><td> $rent</td><td></td><td></td></tr>";
$sec10_line .= "<tr><td> D)House Rent Paid </td><td>$house_rent_paid</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'];
$sec_tax_type = explode(",",$sub_value['income_tax_type']);
//Get declaration entry Value
if(in_array($income_tax_type, $sec_tax_type)){ // Based on OLD and NEW Regime
//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>$pt_actual (Actual)</td><td>$pt_proj (Projection)</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'];
$sub_section_line .= "<tr><td> $tax_subsection_name</td><td>$pf_actual (Actual)</td><td>$pf_proj (Projection)</td><td>$pf_total</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>";
}
}
$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 and cw_income_tax_slab.financial_setting_id = "'.$fin_set_id.'"';
$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 = ($tax_range_end - $tax_range_start)+1;
$tax_value = round(($tax_range * $tax_amount) / 100);
$tax_value = number_format((float) $tax_value, 2, '.', '');
$final_tax = $final_tax - $tax_range;
$tax_slab_line .= "<tr><td> For $tax_range : $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
$tax_slab_query = 'SELECT tax_range_start,tax_range_end,tax_amount_percentage as tax_amount,cw_slap_type.slap_type,cw_slap_type.prime_slap_type_id FROM `cw_income_tax_slab_new_income_tax_slab_details_new` inner join cw_income_tax_slab_new on cw_income_tax_slab_new.prime_income_tax_slab_new_id = cw_income_tax_slab_new_income_tax_slab_details_new.prime_income_tax_slab_new_id inner join cw_slap_type on cw_slap_type.prime_slap_type_id = cw_income_tax_slab_new.slab_type WHERE min_age <= "' . $emp_age . '" and max_age >= "' . $emp_age . '" and cw_income_tax_slab_new_income_tax_slab_details_new.trans_status = 1 and cw_income_tax_slab_new.financial_setting_id = "'.$fin_set_id.'" ';
$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_range = ($tax_range_end - $tax_range_start)+1;
$tax_value = round(($tax_range * $tax_amount) / 100);
$tax_value = number_format((float) $tax_value, 2, '.', '');
$final_tax = $final_tax - $tax_range;
$tax_slab_line .= "<tr><td> For $tax_range : $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>";
} // New Tax End
$surcharge_pct = $process_sheet_array[$process_month][$employee_code]['surcharge_pct'];
$surcharge_charge = $process_sheet_array[$process_month][$employee_code]['surcharge_amt'];
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 <= $rebate){
$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){
$cess_value = $process_sheet_array[$process_month][$employee_code]['edu_cess'];
$cess_tax = $process_sheet_array[$process_month][$employee_code]['tax_amt'];
$net_tax = $process_sheet_array[$process_month][$employee_code]['net_tax'];
if($cess_value > 0){
$edu_cess_line .= "<tr><td> Education Cess $edu_cess % of $net_tax </td><td></td><td></td><td>$cess_value</td></tr>";
}
//relief under section 89 values
$relief_line = "<tr><td> Net Tax</td><td></td><td></td><td>$net_tax</td></tr>
<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
$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 = "";
} //page-break-inside: avoid;
$table_data = "<div style='width:700px; margin-left: auto; margin-right: auto; padding: 15px; background-color: #EEEEEE; border-radius: 3px; box-shadow: 0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2);'>
<table style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;text-align: left;font-size: 14px;background-color: #FFFFFF;' border='1' cellspacing='0' cellpadding='4'>
<tbody>
<tr>
<td colspan='2'>
<h3 style='color:#d3434d;margin:10px;font-size:21px;text-align: center;'>TDS WORK SHEET</h3>
</td>
</tr>
<tr>
<td>Name and address of the employer</td>
<td>Code,name and designation of the employee</td>
</tr>
<tr>
<td style='font-weight:bold;'>$company_name<br/>$company_address<br/>$company_city<br/>$company_state<br/>$company_country</td>
<td>$employee_code<br/>$emp_name<br/>$designation<br/>$start_text - $end_text<br/>Declaration Wise</td>
</tr>
</tbody>
</table>
<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>
<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>";
//echo $table_data; die;
$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
));
}
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,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');
$category_name = strtolower(str_replace(" ","_",$cat_name));
// 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_projection/".$category_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 "BSK ".base_url().$folder."/".$employee_code.".pdf"; die;
echo trim(base_url().$folder."/".$employee_code.".pdf");
}
//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;
}
}
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;
}
}
?>