File: /home/cafsindia/hrms_allyindian_com/application_bk/controllers/Worksheet_generation.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
use Dompdf\Dompdf;
class Worksheet_generation extends Action_controller{
public function __construct(){
parent::__construct('worksheet_generation');
// $this->load->library('pdf');
}
// LOAD PAGE WITH TABLE DATA
public function index(){
//PAGE INFO FUNCTION
$this->page_info();
$data['encKey'] = $this->generateKey();
$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(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$search_term = $this->input->post('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(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$this->save_info();
$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');
$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);
}
$annexure_sts_val = 0;
if($annexure_sts === "on"){
$annexure_sts_val = 1;
}
//Check Incometax lock exist
$exist_qry = 'select count(prime_income_tax_lock_id) as income_tax_count from cw_income_tax_lock where tax_month = "'.$process_month.'" and income_tax_status = 1 and trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$exist_data->next_result();
$num_rows = $exist_result[0]->income_tax_count;
if((int)$num_rows > 0){
echo json_encode(array('success' => false, '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','$payroll_exist_qry')");
$payroll_exist_result = $payroll_exist_data->result();
$payroll_exist_data->next_result();
$num_rows = $payroll_exist_result[0]->payroll_count;
if((int)$num_rows > 0){
echo json_encode(array('success' => false, 'msg' => 'Payroll Locked for this Month'));
exit(0);
}
$process_dt = new DateTime("01-$process_month 00:00:00");
$process_dt->modify('-1 month');
$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 = "";
if ($process_type === 1){
$where_query = ' and employee_code = "' . $process_emp_code . '" ';
$cond_query = ' and cw_employees.employee_code = "' . $process_emp_code . '" ';
}else if ($process_type === 2){
$where_query = ' and role = "' . $process_role . '"';
$cond_query = ' and cw_employees.role = "' . $process_role . '"';
}
//Get Employee Query
$pwd_column = $this->company_info[0]->password_column;//get password column from employees
$pass_qry = "";
if($pwd_column){ // get this column if password column exist
$pass_qry = ",$pwd_column";
}
$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,cw_employees.last_working_date '.$pass_qry.' 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_tax_calculation on cw_employees.employee_code = cw_tax_calculation.emp_code WHERE cw_employees.trans_status = 1 and cw_tax_calculation.process_month = "'.$process_month.'" and cw_tax_calculation.trans_status = 1 and (cw_employees.date_of_joining <= "' . $end_date . '" and cw_employees.termination_status = 0 or cw_employees.last_working_date >= "' . $start_date . '" and cw_employees.termination_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();
//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,
'msg' => "Section 10 Excemption Details not Mapped..!"
));
exit(0);
}
//For Display in PDF
$process_date = date("Y-m-d", strtotime("01-" . $process_month));
//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;
}
}
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;
//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)
$other_income_qry = 'select cw_income_type.income_type,income_description,other_income_column,annual_limit,cw_other_income.income_type as income_type_id 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 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();
//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);
}
//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);
$formula_array = explode(",", $earnings_result[0]->formula);
$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 employee_code,role,'.$column_label.' from cw_transactions where cw_transactions.trans_status = 1 and cw_transactions.transactions_month = "'.$process_month.'" and cw_transactions.role != 1 ';
$emp_sal_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_sal_query')");
$emp_sal_rslt = $emp_sal_data->result_array();
$emp_sal_data->next_result();
$trans_array = array();
foreach ($emp_sal_rslt as $key => $value){
$trans_array[$value['employee_code']] = $value;
}
// 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;
}
}
//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($emp_rslt){
$emp_rslt_arr = json_decode(json_encode($emp_rslt),true);
$emp_array = array();
foreach ($emp_rslt_arr as $key => $value){ // Convert as array to get data based on employee code
$emp_array[$value['employee_code']] = $value;
}
$this->process_worksheet($emp_rslt,$process_month,$annexure_sts_val,$sec10_result,$other_income_result,$hra_result,$section_array,$earnings_result_array,$trans_array,$column_rslt,$emp_array,$process_type,$process_emp_code,$process_role);
exit(0);
}else{
echo json_encode(array(
'success' => False,
'msg' => "No Records Found...!"
));
exit(0);
}
}
//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');
$begin->modify('first day of next month');
$month_td .= "<td><b>$month_list</b></td>";
$arr[$month] = 0;
}
$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;
}
public function process_worksheet($emp_rslt,$process_month,$annexure_sts_val,$sec10_result,$other_income_result,$hra_result,$section_array,$earnings_result_array,$trans_array,$column_rslt,$emp_array,$process_type,$process_emp_code,$process_role){
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;
$month_name = date("F", strtotime("01-" . $process_month));
$pro_where_qry = '';
if((int)$process_type === 1){ // PROCESS BY EMPLOYEE CODE [MS 05-02-2025]
$pro_where_qry = ' AND emp_code = "'.$process_emp_code.'"';
}else{ // PROCESS BY ROLE
$pro_where_qry = ' AND emp_role = "'.$process_role.'"';
}
$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 '.$pro_where_qry;
$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);
}
//Dynamic column list array
$get_column_qry = 'select * from cw_payroll_function_map where trans_status=1';
$get_column_data = $this->db->query("CALL sp_a_run ('SELECT','$get_column_qry')");
$get_column_result = $get_column_data->result();
$get_column_data->next_result();
$monthly_tds_db = "";
$one_time_tds_db = "";
foreach ($get_column_result as $column_name){
$loc_name = $column_name->loc_name;
$db_column = $column_name->db_column;
if ($loc_name == "one_time_tds"){
$one_time_tds_db = $db_column;
}else
if($loc_name == "monthly_tds"){
$monthly_tds_db = $db_column;
}
}
//Get TDS Data
$tds_query = 'SELECT SUM('.$monthly_tds_db.') as monthly_tds,SUM('.$one_time_tds_db.') as onetime_tds 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;
$tds_data = $this->db->query("CALL sp_a_run ('SELECT','$tds_query')");
$tds_result = $tds_data->result_array();
$tds_data->next_result();
$tds_result_array = [];
foreach ($tds_result as $arr) {
$tds_result_array[$arr['transactions_month']][$arr['employee_code']] = $arr;
}
$cur_tds_query = 'SELECT employee_code,SUM('.$monthly_tds_db.') as cur_monthly_tds FROM cw_transactions WHERE cw_transactions.trans_status = 1 and transactions_month = "'.$process_month.'" GROUP BY employee_code';
$cur_tds_data = $this->db->query("CALL sp_a_run ('SELECT','$cur_tds_query')");
$cur_tds_result = $cur_tds_data->result_array();
$cur_tds_data->next_result();
$cur_tds_result_array = [];
foreach ($cur_tds_result as $arr) {
$cur_tds_result_array[$arr['employee_code']] = $arr;
}
$process_month_dt = "01-".$process_month;
$section_dec_query = 'SELECT a.* FROM cw_declaration_entry as a INNER JOIN (SELECT emp_code, date_format(MAX(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_declaration_entry where finacial_setting_id = "'.$fin_set_id.'" and date_format(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") GROUP BY emp_code) as groupedtt ON a.emp_code = groupedtt.emp_code AND a.effective_month = groupedtt.max_date';
$section_dec_data = $this->db->query("CALL sp_a_run ('SELECT','$section_dec_query')");
$section_dec_result = $section_dec_data->result_array();
$section_dec_data->next_result();
foreach($section_dec_result as $arr){
$section_dec_array[$arr['emp_code']] = $arr;
}
//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);
//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();
$actual_rent = $hra_result[0]->actual_rent_paid_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();
// return $projection_result;
$earnings_columns = explode(",", $earnings_result[0]->earnings);
$view_array = explode(",", $earnings_result[0]->view_name);
$projection_columns = explode(",", $projection_result[0]->projection);
$proj_earnings = explode(",", $projection_result[0]->proj_earnings);
$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;
$category_name = $emp->category_name;
$date_of_joining = $emp->date_of_joining;
$last_working = $emp->last_working_date;
$emp_age = $emp->emp_age;
$designation = $emp->designation;
$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){
$sd = $financial_info[0]->old_sd;
$rebate = $financial_info[0]->old_rebate;
$margin_relief = $financial_info[0]->old_m_relief;
}else{
$sd = $financial_info[0]->new_sd;
$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
}
if($date_of_joining > $start_date && $date_of_joining < $end_date){
$start_text = date("d-M-Y", strtotime($date_of_joining));
}
if($last_working > $start_date && $last_working < $end_date){
$end_text = date("d-M-Y", strtotime($last_working));
}
/** Earning Part Start - BSK **/
$tr_line = "";
$earned_array = array();
$annexure_data = 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;
$projection_column = $proj_combine[$value];
$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 ($other_income_result as $gross_key => $gross_value){
$income_type = $gross_value->income_type;
$income_type_id = $gross_value->income_type_id;
$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((int)$income_type_id === 3 || (int)$income_type_id === 4 || (int)$income_type_id === 5){
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;
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);
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 = $section_dec_array[$employee_code][$tax_subsection_column];
$act_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>$act_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_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'];
$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 = $section_dec_array[$employee_code][$tax_subsection_column];
$act_value = $process_sheet_array[$process_month][$employee_code][$tax_subsection_column];
// if($tax_subsection_limit === "0.00"){
// $act_value = $act_value;
// }
// else{
// $act_value = min($act_value, $tax_subsection_limit);
// }
$match_value = $process_sheet_array[$process_month][$employee_code]['section_matching'];
if ($tax_subsection_column === "taxsubsec_8") {
$dec_value = $sd;
$sub_section_line .= "<tr><td> $tax_subsection_name</td><td>$dec_value</td><td></td><td>$act_value</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>$act_value</td></tr>";
}
}else{
if(($act_value === "") && ($act_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>$act_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;
if((int)$income_type_id === 1 || (int)$income_type_id === 2){
$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_income_tax_slab on cw_income_tax_slab.prime_income_tax_slab_id = cw_income_tax_slab_income_tax_slab_details.prime_income_tax_slab_id inner join cw_slap_type on cw_slap_type.prime_slap_type_id = cw_income_tax_slab.slap_type WHERE min_age <= "' . $emp_age . '" and max_age >= "' . $emp_age . '" and 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
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>";
}
//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>";
}
//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";
$prev_tax_line = "";
$tax_pay_line = "";
//finding previous tax calculation values// map tds or default values is monthly tds
//Tax financial setting date details
$date = new DateTime("01-$process_month 00:00:00");
$date->modify('-1 month');
$prev_tax = 0;
$prev_tax = $process_sheet_array[$process_month][$employee_code]['tax_deducted'];
$cur_month_tax = $cur_tds_result_array[$employee_code]['cur_monthly_tds'];
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'] - $cur_month_tax;
$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'];
$month_count_rslt = $month_count_rslt-1;
//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>$cur_month_tax</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; '>
<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>";
$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 get_months($start_date, $end_date){
$start = new DateTime($start_date);
$end = new DateTime($end_date);
$start->modify('first day of this month');
$end->modify('last day of this month');
$interval = DateInterval::createFromDateString('1 month');
$period = new DatePeriod($start, $interval, $end);
return $period;
}
public function month_count($process_date,$end_date){
$process_date = strtotime($process_date);
$end_date = strtotime($end_date);
$process_year = date('Y', $process_date);
$end_year = date('Y', $end_date);
$process_month = date('m', $process_date);
$end_month = date('m', $end_date);
$diff = (($end_year - $process_year) * 12) + ($end_month - $process_month) + 1;
return $diff;
}
public function work_sheet_generate_pdf(){
// use Dompdf\Dompdf;
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$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();
$dompdf = new DOMPDF(array('enable_remote' => true,"enable_font_subsetting" => true));
// Load HTML content
$dompdf->loadHtml($content);
// (Optional) Setup the paper size and orientation
$dompdf->setPaper('A3', 'portrait');
// Render the HTML as PDF
$dompdf->render();
if($password){
define('DOMPDF_ENABLE_AUTOLOAD', false);
$dompdf->get_canvas()->get_cpdf()->setEncryption($password, $password);
}
// Output the generated PDF (1 = download and 0 = preview)
$output = $dompdf->output();
$folder = "worksheet/".$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");
}
$company_information =$this->company_info();
$encrypted_pdf = $company_information[0]->encrypted_pdf;
if((int)$encrypted_pdf === 1){
$db_name = $this->config->item("db_name");
$file_name = $db_name."_".$employee_code."_".$process_month;
$enc_file = $this->encryptFilename($file_name,$db_name);
$enc_file_name = $enc_file."_".$employee_code;
}else{
$enc_file_name = $employee_code;
}
file_put_contents($folder."/".$enc_file_name.".pdf", $output);
//Encryption
$enc_path = base64_encode(base_url().$folder."/".$enc_file_name.".pdf");
echo json_encode(array('file' => $enc_file_name,'path' => $enc_path,'folder' => $folder));
}
//FUNCTION FOR DOWNLOAD ZIP FILE
public function download_zip(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$emp_codes = $this->input->post("emp_codes");
$emp_codes = str_replace('"', '', $emp_codes);
$emp_code_arr = explode(',', $emp_codes);
$path_name = strtolower($this->input->post('path_name'));
$path = "./".$path_name."/";
$zip = new ZipArchive();
$filename = "./".$path_name.".zip";
unlink($filename);
if ($zip->open($filename, ZipArchive::CREATE)!==TRUE){
exit("cannot open <$filename>\n");
}
$dir = $path;
if(is_dir($dir)){
if($dh = opendir($dir)){
while(($file = readdir($dh)) !== false){
if($file != '' && $file != '.' && $file != '..'){
$file_name = $path;
if(is_dir($file_name)){
if($dhs = opendir($file_name)){
while(($files = readdir($dhs)) !== false){
if(in_array($files, $emp_code_arr)){
if(is_file($file_name.$files)){
if($files != '' && $files != '.' && $files != '..'){
$zip->addFile($file_name.$files);
}
}
}
}
closedir($dhs);
}
}
}
}
closedir($dh);
}
}
$zip->close();
$filename = str_replace("./","",$filename);
echo json_encode(array('success' => true,'filename' => $filename));
}
}
?>