File: /home/cafsindia/uds.cafsinfotech.in/smart_hrms_dev/application/controllers/Form_16_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Form_16_report extends Action_controller{
public function __construct(){
parent::__construct('form_16_report');
$this->load->library('pdf');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 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['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//LOAD MODEL PAGE VIEW WITH DATA
public function view($form_view_id=-1){
//VIEW INFO FUNCTION
$this->view_info($form_view_id);
// $data['module_sts'] = (int)$this->module_sts;
//VIEW, FORM INPUT
$data['view_info'] = $this->view_info;
$data['form_info'] = $this->form_info;
$data['formula_result'] = $this->formula_result;//for label color change
$data['role_based_condition'] = $this->role_based_condition;//for label name readonly options
$data['all_pick'] = $this->pick_list;//all pick result
//DEPENDENT AUTO PICKLIST
$data['auto_pick'] = $this->depen_auto_list;//dependent auto result
$data['condition_list'] = $this->form_condition_list;
//VIEW DATA
$base_query = str_replace("@SELECT@",$this->view_select,$this->base_query);
$form_view_qry = $base_query .' where '.$this->prime_table.'.'.$this->prime_id.' = '.$form_view_id.' and '.$this->prime_table.'.trans_status = 1';
$row_view_qry = 'select * from cw_form_view_setting where prime_view_module_id = "'.$this->control_name.'" and form_view_type = "3" and trans_status = 1';
$view_pro_qry = [];
$view_pro_qry[] = array("return"=>"form_view","qry"=>$form_view_qry);
$view_pro_qry[] = array("return"=>"row_view_list","qry"=>$row_view_qry);
$view_info_rslt = $this->run_multi_qry($view_pro_qry);
$form_view_rslt = $view_info_rslt->rslt->form_view[0];
$row_view_rslt = $view_info_rslt->rslt->row_view_list;
$data['form_view'] = $form_view_rslt;
$row_view_list = array();
foreach($row_view_rslt as $view){
$prime_form_view_id = $view->prime_form_view_id;
$row_set_data = $this->get_row_set_data($prime_form_view_id,$form_view_id);
$row_view_list[$prime_form_view_id] = $row_set_data;
}
$data['row_view_list'] = $row_view_list;
$data['edit_id'] = $form_view_id;
//FOR DEPENDENT
$data['get_depend_prime_id'] = $this->get_depend_fun();
$data['depend_label_id'] = $this->depend_label_fun();
$this->load->view("$this->control_name/form",$data);
}
//IMPORT FILE VIEW INFORMATION
public function import(){
$data['module_id'] = $this->control_name;
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "'.$this->control_name.'" and trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_format_drop[""] = "---- Excel Format ----";
foreach($excel_result as $excel){
$prime_excel_format_id = $excel->prime_excel_format_id;
$excel_name = $excel->excel_name;
$excel_format_drop[$prime_excel_format_id] = $excel_name;
}
$data['excel_format_drop'] = $excel_format_drop;
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/import",$data);
}
//start to form generation
public function emp_suggest(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$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);
}
public function form_generate(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$process_type = (int)$this->input->post('process_type');
$process_emp_code = $this->input->post('process_emp_id');
$process_role = implode(',', $this->input->post('process_role') ?? []);
$process_mode = (int)$this->input->post('process_mode');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
$company_info = $this->company_info();
$company_name = $company_info[0]->company_name;
$company_addr = $company_info[0]->address;
$city = $company_info[0]->city;
$pincode = $company_info[0]->pincode;
$financial_info = $this->get_financial_year();
$fin_id = $financial_info[0]->prime_financial_setting_id;
$start_date = $financial_info[0]->start_date;
$end_date = $financial_info[0]->end_date;
$date = new DateTime($end_date);
$monthYearFin = $date->format('m-Y');
$tan_no = $financial_info[0]->tan_no;
$pan_no = $financial_info[0]->pan_or_gnr_no;
$ass_start_year = date("Y", strtotime($end_date));
$ass_end_year = $ass_start_year+1;
$fin_strt_year = date("Y", strtotime($start_date));
$fin_end_year = date("Y", strtotime($end_date));
if($process_type === 1){
$cond_query = 'AND cw_tax_calculation.emp_code = "'.$process_emp_code.'" ';
$dec_query = 'AND cw_declaration_entry.emp_code = "'.$process_emp_code.'" ';
}elseif($process_type === 2){
$cond_query = 'AND cw_tax_calculation.emp_role in ('.$process_role.')';
$dec_query = 'AND cw_declaration_entry.category in ('.$process_role.')';
}else{
$cond_query = "";
$dec_query = "";
}
//Form 24Q Excel Generations
if($process_mode == 2){
$excel_rslt = $this->get_24Q_excel_data($cond_query,$dec_query);
$sts = 1;
echo json_encode(array('success' => TRUE, 'excel_rslt' => $excel_rslt,'sts' => $sts));
exit(0);
}else{
$sts = 2;
//incharge name and designation info
$incharge_data_qry = 'select * from cw_employees inner join cw_financial_setting on cw_financial_setting.incharge_employee=cw_employees.employee_code inner join cw_position on cw_position.prime_position_id = cw_employees.designation where cw_employees.trans_status =1 and cw_employees.termination_status = 0 and cw_employees.role != 1 and prime_financial_setting_id = "'.$fin_id.'"';
$incharge_data = $this->db->query("CALL sp_a_run ('SELECT','$incharge_data_qry')");
$incharge_data_result = $incharge_data->result();
$incharge_data->next_result();
$incharge_name = $incharge_data_result[0]->emp_name;
$incharge_designation = $incharge_data_result[0]->position_name;
$incharge_father_name = $incharge_data_result[0]->emp_father_name;
$incharge_place = $incharge_data_result[0]->place;
$sign_date = date("d/m/Y");
$position_qry = 'select prime_position_id,position_name from cw_position where trans_status=1';
$position_info = $this->db->query("CALL sp_a_run ('SELECT','$position_qry')");
$position_result = $position_info->result();
$position_info->next_result();
$position_arr = array();
foreach($position_result as $arr){
$position_arr[$arr->prime_position_id]=$arr->position_name;
}
$emp_trans_qry = 'SELECT employee_code,IFNULL(sum(monthly_tds),0) as monthly_tds from cw_transactions where trans_status =1 and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") BETWEEN date_format("'.$start_date.'", "%Y-%m") and date_format("'.$end_date.'", "%Y-%m") GROUP BY employee_code';
$emp_trans_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_trans_qry')");
$emp_trans_result = $emp_trans_data->result_array();
$emp_trans_data->next_result();
//employee wise data
$emp_trans_arr = array();
foreach ($emp_trans_result as $key => $arr) {
$emp_trans_arr[$arr['employee_code']] = $arr['monthly_tds'];
}
$challan_line = "";
$challan_entry = "";
if($process_mode === 3){
$challan_qry = 'select emp_code,cw_challan_entry_line.taxable_amt,cw_challan_entry_line.tot_tax_ded,cw_challan_entry_line.challan_no,cw_challan_entry_line.tot_tax_dep,cw_challan_entry_line.cheque_no,bsr_code,cw_challan_entry_line.deposit_date from cw_challan_entry inner join cw_challan_entry_line on cw_challan_entry_line.challan_entry_id = cw_challan_entry.prime_challan_entry_id INNER JOIN cw_bank ON cw_challan_entry_line.bank_name = cw_bank.prime_bank_id where cw_challan_entry_line.trans_status=1 and cw_challan_entry.trans_status=1 and financial_setting_id = "'.$fin_id.'" GROUP BY cw_challan_entry_line.challan_no,cw_challan_entry_line.emp_code order by cw_challan_entry_line.deposit_date ASC';
$challan_info = $this->db->query("CALL sp_a_run ('SELECT','$challan_qry')");
$challan_result = $challan_info->result();
$challan_info->next_result();
$q_arr = array();
foreach($challan_result as $challan){
$emp_code = $challan->emp_code;
$challan_no = $challan->challan_no;
$q_arr[$emp_code][$challan_no][] = $challan;
}
//print_r($q_arr); die;
}
$td_class= "border-right: solid 1px black; border-left: solid 1px black;";
//need to start to process
$form_temp_qry = 'select * from `cw_form16_template` inner join cw_form_sixteen on cw_form_sixteen.prime_form_sixteen_id=cw_form16_template.prime_form_sixteen_id where cw_form_sixteen.trans_status = 1 and fin_set_id = "'.$fin_id.'" and cw_form16_template.prime_form_sixteen_id !=1 order by tax_display_order asc';
$form_temp_data = $this->db->query("CALL sp_a_run ('SELECT','$form_temp_qry')");
$form_temp_result = $form_temp_data->result();
$form_temp_data->next_result();
$tax_process_exit_qry = 'select count(*) as rslt_count from `cw_tax_calculation` where trans_status = 1 and fin_set_id = "'.$fin_id.'" ';
$tax_process_exit_data = $this->db->query("CALL sp_a_run ('SELECT','$tax_process_exit_qry')");
$tax_process_exit_result = $tax_process_exit_data->result();
$tax_process_exit_data->next_result();
$tax_rslt_count = $tax_process_exit_result[0]->rslt_count;
if((int)$tax_rslt_count === 0){
echo json_encode(array('success' => False, 'msg' => "Tax is not processed for this finanncial year!"));
exit(0);
}else{
$form_exit_qry = 'select count(*) as form_rslt_count from `cw_form16_template` where trans_status = 1 and fin_set_id = "'.$fin_id.'"';
$form_exit_data = $this->db->query("CALL sp_a_run ('SELECT','$form_exit_qry')");
$form_exit_result = $form_exit_data->result();
$form_exit_data->next_result();
$form_rslt_count = $form_exit_result[0]->form_rslt_count;
if((int)$form_rslt_count === 0){
echo json_encode(array('success' => False, 'msg' => "Form Template is not created for this finanncial year!"));
exit(0);
}else{
$tax_process_exit_qry = 'SELECT COUNT(*) AS counter FROM cw_tax_calculation WHERE fin_set_id = '.$fin_id.' '.$cond_query.' AND trans_status = 1 ; ';
$tax_process_exit_data = $this->db->query("CALL sp_a_run ('SELECT','$tax_process_exit_qry')");
$tax_process_exit_result = $tax_process_exit_data->result();
$tax_process_exit_data->next_result();
$tax_rslt_count = $tax_process_exit_result[0]->counter;
if((int)$tax_rslt_count === 0){
echo json_encode(array('success' => False, 'msg' => "No Records Found!"));
exit(0);
}else{
$process_month_dt = "01-".$monthYearFin;
$process_emp_qry = 'SELECT groupedtt.* ,a.* FROM cw_tax_calculation as a INNER JOIN (SELECT e.*, emp_code,date_format(MAX(str_to_date(CONCAT("01-", cw_tax_calculation.process_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_tax_calculation INNER JOIN cw_employees AS e ON cw_tax_calculation.emp_code = e.employee_code where fin_set_id = "'.$fin_id.'" '.$cond_query.' and date_format(str_to_date(CONCAT("01-", cw_tax_calculation.process_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") and cw_tax_calculation.trans_status = 1 GROUP BY emp_code) as groupedtt ON a.emp_code = groupedtt.emp_code AND a.process_month = groupedtt.max_date and a.trans_status = 1 and fin_set_id = "'.$fin_id.'"';
$process_emp_data = $this->db->query("CALL sp_a_run ('SELECT','$process_emp_qry')");
$process_emp_result = $process_emp_data->result();
$process_emp_data->next_result();
$tax_data = array();
$pdf_data = array();
$challan_entry = "";
$challan_line = "";
foreach($process_emp_result as $emp_rslt => $val){
$emp_code = $val->emp_code;
$emp_name = $val->emp_name;
$pan_number = $val->pan_number;
$emp_designation = $val->designation;
$emp_active_sts = $val->termination_status;
$last_working_date = $val->last_working_date;
$tax_amt = $val->tax_amt;
$emp_position = $position_arr[$emp_designation];
$prev_ded = $emp_trans_arr[$emp_code];
$format_frm_date = date("jS M Y", strtotime($start_date));
if((int)$emp_active_sts === 0){
$format_end_date = date("jS M Y", strtotime($end_date));
}else{
$format_end_date = date("jS M Y", strtotime($last_working_date));
}
foreach($val as $emp_key =>$emp_val){
$tax_data[$emp_key] = $emp_val;
}
$challan_arr = $q_arr[$emp_code];
//Challan Details
$s = 1;
$tot_dep = 0;
$taxable = 0;
$tot_tax = 0;
foreach($challan_arr as $challan_no => $result){
foreach ($result as $key => $value) {
$challan_entry .= "<tr><td>".$s."</td><td>".$value->tot_tax_dep."</td><td>".$value->cheque_no."</td><td>".$value->bsr_code."</td><td>".date("d-m-Y",strtotime($value->deposit_date))."</td><td>".$value->challan_no."</td></tr>";
$taxable = $taxable + $value->taxable_amt;
$tot_tax = $tot_tax + $value->tot_tax_ded;
$tot_dep = $tot_dep + $value->tot_tax_dep;
$s++;
}
/*$challan_line .= "<tr><td>Q".$q."</td><td>".$challan_no."</td><td>".$taxable."</td><td>".$tot_tax."</td><td>".$tot_dep."</td></tr>";
$q++;*/
}
//echo "BSK $tot_tax :: $prev_ded :: $tot_dep "; die;
$final_tax = $tax_amt - $prev_ded;
$tot_dep_words = $this->numbertowords($tot_dep);
$tot_dep_words = strtoupper($tot_dep_words);
$challan_entry .= "<tr><td>Total Rs. </td><td style='font-weight:bold;'>".$tot_dep."</td><td></td><td></td><td></td><td></td></tr>";
$tr_line ="<tr><td style='width:70%;$td_class'></td><td style='text-align:center;width:10%;$td_class'>Rs.</td><td style='text-align:center;width:10%;$td_class'>Rs.</td><td style='text-align:center;width:10%;$td_class'>Rs.</td></tr>";
foreach($form_temp_result as $form_tmp_rslt){
$form_id = $form_tmp_rslt->prime_form_sixteen_id;
$tax_act = $form_tmp_rslt->tax_act;
$form16_key = $form_tmp_rslt->form16_key;
$form16_value = $form_tmp_rslt->form16_value;
$arr = explode("+", $form16_value);
if(count($arr) > 1){
$tax_value = 0;
foreach ($arr as $key => $value) {
$tax_value = $tax_value + $tax_data[$value];
}
}else{
$tax_value = $tax_data[$form16_value];
}
if($form16_value === 'taxsubsec_1'){
$tax_value = $tax_data['hra'];
}
if($tax_value > 0){
$tax_value = "Rs. ".$tax_value;
}
if(strstr($form16_value, 'tot')){
$tr_line .="<tr><td style='width:70%;$td_class'>$tax_act</td><td style='text-align:right;width:10%;$td_class'></td><td style='text-align:right;width:10%;$td_class'></td><td style='text-align:right;width:10%;$td_class'>$tax_value</td></tr>";
}elseif((int)$form_id === 7){
if(empty($tax_value) || $tax_value =="0.00"){
$tr_line .= "";
}
}elseif((int)$form_id === 7){
if(empty($tax_value) || $tax_value =="0.00"){
$tr_line .= "";
}
}elseif((int)$form_id === 15){
if(empty($tax_value) || $tax_value =="0.00"){
$tr_line .= "";
}
}elseif((int)$form_id === 16){
if(empty($tax_value) || $tax_value =="0.00"){
$tr_line .= "";
}
}elseif((int)$form_id === 17){
if(empty($tax_value) || $tax_value =="0.00"){
$tr_line .= "";
}
}elseif((int)$form_id === 18){
if(empty($tax_value) || $tax_value =="0.00"){
$tr_line .= "";
}
}elseif((int)$form_id === 19){
if(empty($tax_value) || $tax_value =="0.00"){
$tr_line .= "";
}
}elseif((int)$form_id === 46){
if(empty($tax_value) || $tax_value =="0.00"){
$tr_line .= "";
}
}elseif((int)$form_id === 47){
if(empty($tax_value) || $tax_value =="0.00"){
$tr_line .= "";
}
}elseif((int)$form_id === 48){
if(empty($tax_value) || $tax_value =="0.00"){
$tr_line .= "";
}
}elseif((int)$form_id === 49){
if(empty($tax_value) || $tax_value =="0.00"){
$tr_line .= "";
}
}elseif((int)$form_id === 50){
if(empty($tax_value) || $tax_value =="0.00"){
$tr_line .= "";
}
}elseif((strstr($form16_value, 'taxsubsec')) || (strstr($form16_value, 'otherin')) || (strstr($form16_value, 'gross_17'))){
$tr_line .="<tr><td style='width:40%;$td_class'>$tax_act</td><td style='text-align:right;width:20%;$td_class'></td><td style='text-align:right;width:20%;$td_class'>$tax_value</td><td style='text-align:right;width:20%;$td_class'></td></tr>";
}elseif((int)$form_id === 34){
$tr_line .="<tr><td style='width:40%;$td_class'>$tax_act</td><td style='text-align:right;width:20%;$td_class'></td><td style='text-align:right;width:20%;$td_class'></td><td style='text-align:right;width:20%;$td_class'></td></tr>";
$tr_line .="<tr><td style='width:40%;$td_class'></td><td style='text-align:right;width:20%;$td_class'></td><td style='text-align:right;width:20%;$td_class'>Gross Amount</td><td style='text-align:right;width:20%;$td_class'>Deductible Amount</td></tr>";
}elseif((int)$form_id === 42){
$tr_line .="<tr><td style='width:40%;$td_class'>$tax_act</td><td style='text-align:right;width:20%;$td_class'></td><td style='text-align:right;width:20%;$td_class'></td><td style='text-align:right;width:20%;$td_class'></td></tr>";
$tr_line .="<tr><td style='width:40%;$td_class'></td><td style='text-align:right;width:20%;$td_class'>Gross Amount</td><td style='text-align:right;width:20%;$td_class'>Qualifying Amount</td><td style='text-align:right;width:20%;$td_class'>Deductible Amount</td></tr>";
}else{
$tr_line .="<tr><td style='width:40%;$td_class'>$tax_act</td><td style='text-align:right;width:20%;$td_class'></td><td style='text-align:right;width:20%;$td_class'></td><td style='text-align:right;width:20%;$td_class'>$tax_value</td></tr>";
}
}
if($process_mode == 3){
$table_data = "
<h3 style='margin:10px;font-size:14px;text-align: center;'>FORM NO. 16 <br>[See rule 31(1)(a)] </h3>
<h4 style='margin:10px;font-size:11px; text-align: center;'>Certificate under section 203 of the Income-tax Act,1961. for tax deducted at source from income chargeable under the head ''Salaries''</h4>
<table border='1' cellpadding='4' cellspacing='0' style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;text-align: left;font-size: 12px;background-color: #FFFFFF;'>
<tbody>
<tr>
<td colspan='2' style='height:20px; width:50%; text-align:center;'>
<span style='font-weight:bold; text-decoration: underline; line-height: 2.5;'>Name and Address of the Employer:</span><br>
<span style='text-align:center;'>$company_name</span> <br><span style='text-align:center;'>$company_addr</span>
</td>
<td colspan='3' style='height:20px; width:50%; text-align:center; line-height: 1.5;'>
<span style='text-align:center; font-weight:bold; text-decoration: underline;'>Name and Designation of the Employee:</span><br>
<span style='text-align:center;'>$emp_name </span><br><span style='text-align:center;'>$emp_position</span>
</td>
</tr>
<tr>
<td colspan='2' style='height:20px; text-align:center;'>
<span style='font-weight:bold;'>PAN of the Deductor TAN of the Deductor</span>
<span>$pan_no $tan_no</span>
</td>
<td colspan='3' style='height:20px; text-align:center;'>
<span style='text-align:center; font-weight:bold;'>PAN of the Employee</span><br>
<span style='text-align:center;'>$pan_number</span>
</td>
</tr>
<tr>
<td colspan='2' rowspan='2' style='height:20px;'>
<span style='font-weight:bold;'> City:</span> <span>$city</span> <span style='font-weight:bold;'> Pincode:</span><span> $pincode</span>
</td>
<td style='text-align:center;'>
<span style='text-align:center; font-weight:bold;'>Assessment Year</span>
</td>
<td colspan='2' style='text-align:center;'>
<span style='text-align:center; font-weight:bold;'>Period</span>
</td>
</tr>
<tr>
<td>
<div style='text-align: center;'>$ass_start_year - $ass_end_year </div>
</td>
<td colspan='2' style='text-align:center;'>
<div>$format_frm_date - $format_end_date</div>
</td>
</tr>
<tr>
<td colspan='5' style='text-align:center; font-weight:bold;'>
<span style='text-align:center; font-weight;bold;'>Summary of Tax deducted at source in respect of deductee</span>
</td>
</tr>
</tbody>
</table>
<h3 style='margin:10px;font-size:14px;text-align: center;'>DETAILS OF SALARY PAID AND ANY OTHER INCOME AND TAX DEDUCTED</h3>
<table style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;text-align: left;font-size: 12px;background-color: #FFFFFF;' border='1' cellspacing='0' cellpadding='4'>
<tbody>
$tr_line
<tr><td style='width:40%;$td_class'>20. Tax Deducted At Source</td><td style='text-align:right;width:20%;$td_class'></td><td style='text-align:right;width:20%;$td_class'></td><td style='text-align:right;width:20%;$td_class'>Rs. $prev_ded</td></tr>
<tr><td style='width:40%;$td_class'>21. Remaining Tax Payable</td><td style='text-align:right;width:20%;$td_class'></td><td style='text-align:right;width:20%;$td_class'></td><td style='text-align:right;width:20%;$td_class'>$final_tax</td></tr>
</tbody>
</table>
<p style='page-break-after: always;'></p>
<h3 style='margin:10px;font-size:14px;text-align: center;'>DETAILS OF TAX DEDUCTED AND DEPOSITED INTO CENTRAL GOVERNMENT ACCOUNT</h3>
<table style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;text-align: left;font-size: 12px;background-color: #FFFFFF;' border='1' cellspacing='0' cellpadding='4'>
<tr>
<td style='text-align:center;'>S.No</td>
<td style='text-align:center;'>Tax Deposited in respect on<br>of employee (Rs.)</td>
<td style='text-align:center;'>Cheque/DD<br> No(If Any)</td>
<td style='text-align:center;'>BSR Code of <br> Bank Branch</td>
<td style='text-align:center;'>Date on Which <br> Tax Deposited (dd/mm/yy)</td>
<td style='text-align:center;'>Transfer Voucher/ <br> Challan Identification No</td>
</tr>
$challan_entry
</table>
<p style='font-size:14px;'>I $incharge_name, Son of $incharge_father_name working in the capacity of $incharge_designation do hereby certify that a sum of <b> Rs. $tot_dep <b> [$tot_dep_words]<br> has been deducted at source and paid to the credit of the Central Government. I further certify that the information given above is true and correct based on the book of accounts, documents and other available records.</p>
<p style='font-size:14px;'><b>Place:</b> $incharge_place</p>
<p style='font-size:14px;'><b>Date:</b> $sign_date</p>
<p style='margin-left:40% !important;'>Signature of the person responsible for deduction of tax</p>
<p style='margin-left:40% !important; font-size:14px;'>Full name: <b style='font-size:14px;'>$incharge_name</b></p>
<p style='margin-left:40% !important; font-size:14px;'>Designation: <b style='font-size:14px;'>$incharge_designation</b></p>";
$table_data = "<!DOCTYPE html><html> <body>".$table_data."</body></html>";
}else{
$table_data = "
<h3 style='margin:10px;font-size:14px;text-align: center;'>FORM NO. 16<br/>“Part B (Annexure)”<br/>DETAILS OF SALARY PAID AND ANY OTHER INCOME AND TAX DEDUCTED</h3>
<table border='1' cellpadding='4' cellspacing='0' style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;text-align: left;font-size: 12px;background-color: #FFFFFF;'>
<tbody>
<tr>
<td colspan='2' style='height:20px; width:50%; text-align:center;'>
<span style='font-weight:bold; text-decoration: underline; line-height: 2.5;'>Name and Address of the Employer:</span><br>
<span style='text-align:center;'>$company_name</span> <br><span style='text-align:center;'>$company_addr</span>
</td>
<td colspan='2' style='height:20px; width:50%; text-align:center; line-height: 1.5;'>
<span style='text-align:center; font-weight:bold; text-decoration: underline;'>Name and Designation of the Employee:</span><br>
<span style='text-align:center;'>$emp_name </span><br><span style='text-align:center;'>$emp_position</span>
</td>
</tr>
<tr>
<td colspan='2' style='height:20px; text-align:center;'>
<span style='margin-left:50px; font-weight:bold;'>PAN of the Deductor TAN of the Deductor</span>
<span style='margin-left: 50px;'>$pan_no $tan_no</span>
</td>
<td colspan='2' style='height:20px; text-align:center;'>
<span style='text-align:center; font-weight:bold;'>PAN of the Employee</span><br>
<span style='text-align:center;'>$pan_number</span>
</td>
</tr>
<tr>
<td colspan='2' rowspan='2' style='height:20px;'>
<span style='margin-left:20px; font-weight:bold;'> City:</span> <span>$city</span> <span style='font-weight:bold;'> Pincode:</span><span> $pincode</span>
</td>
<td style='text-align:center;'>
<span style='text-align:center; font-weight:bold;'>Assessment Year</span>
</td>
<td style='text-align:center;'>
<span style='text-align:center; font-weight:bold;'>Period</span>
</td>
</tr>
<tr>
<td>
<div style='text-align: center;'>$ass_start_year - $ass_end_year </div>
</td>
<td>
<div>$format_frm_date - $format_end_date</div>
</td>
</tr>
</tbody>
</table>
<table style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;text-align: left;font-size: 12px;background-color: #FFFFFF;' border='1' cellspacing='0' cellpadding='4'>
<tbody>
$tr_line
</tbody>
</table>
<p>I $incharge_name, Son of $incharge_father_name working in the capacity of $incharge_designation do hereby certify that the information given above is true, complete and correct and is based on the books of account, documents, TDS statements, and other available records.</p>
<p><b>Place:</b> $incharge_place</p>
<p><b>Date:</b> $sign_date</p>
<p style='margin-left:40% !important;'>Signature of the person responsible for deduction of tax</p>
<p style='margin-left:40% !important;'>Full name: <b>$incharge_name</b></p>
<p style='margin-left:40% !important;'>Designation: <b>$incharge_designation</b></p>";
$table_data = "<!DOCTYPE html><html> <body><p style='margin-top:0px;text-align:right;'>$emp_code - $emp_name</p><br/><p style='margin-top:0px;text-align:right;'>$pan_number</p>".$table_data."</body></html>";
}
$pdf_data[$emp_code] = $table_data;
}
}
}
}
$count = count($pdf_data ?? []);
echo json_encode(array('success' => TRUE, 'pdf_data' => $pdf_data,'count' => $count,'sts'=>$sts,'get_year'=>$fin_end_year));
}
}
public function form_generate_pdf(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$employee_code = $this->input->post('code');
$content = $this->input->post('html');
$get_year = $this->input->post('get_year');
$set_paper = "portrait";
$folder = "form_16b/form_16b_".$get_year;
$this->dompdf->loadHtml($content);
$this->dompdf->setPaper('A4', 'portrait');
$this->dompdf->render();
$output = $this->dompdf->output();
$oldmask = umask(0);
if(!file_exists($folder)){
mkdir($folder, 0777, true);
}
$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."_".$get_year;
$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);
chmod($folder."/".$enc_file_name.".pdf", 0777);
umask($oldmask);
$path = base64_encode(base_url().$folder."/".$enc_file_name.".pdf");
echo json_encode(array('file' => $enc_file_name,'path' => $path,'folder' => $folder));
}
public function get_24Q_excel_data($cond_query,$dec_query){
$fin_id = $this->financial_info[0]->prime_financial_setting_id;
$start_date = date('d/m/Y', strtotime($this->financial_info[0]->start_date));
$end_date = date('d/m/Y', strtotime($this->financial_info[0]->end_date));
$end_month = date('m-Y', strtotime($this->financial_info[0]->end_date));
$filename = dirname(__FILE__)."/php_excel/PHPExcel/IOFactory.php";
include($filename);
$excel2 = PHPExcel_IOFactory::createReader('Excel5');
$excel2 = $excel2->load('./excel_write/FORM24QA.xls');//default excel template
$excel_sheet = $excel2->setActiveSheetIndex(0);
$high_column = $excel_sheet->getHighestColumn();
$hign_col_num = PHPExcel_Cell::columnIndexFromString($high_column);
$col_name = array();
for($i=0; $i<$hign_col_num; $i++){
$letter = $this->getNameFromNumber($i);
$col_name[] = $letter;
}
$headings = $excel_sheet->rangeToArray('A3:' . $high_column . 3, NULL, TRUE,FALSE);
$head_arr = array_combine($headings[0],$col_name);
//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();
$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;
}
}
//collect the data from tax and employee master
$emp_trans_qry = 'SELECT employee_code,IFNULL(sum('.$monthly_tds_db.'+'.$one_time_tds_db.'),0) as monthly_tds from cw_transactions where trans_status =1 and date_format(str_to_date(concat("01-",transactions_month), "%d-%m-%Y") , "%Y-%m-%d") BETWEEN date_format("'.$this->financial_info[0]->start_date.'", "%Y-%m-%d") and date_format("'.$this->financial_info[0]->end_date.'", "%Y-%m-%d") GROUP BY employee_code';
$emp_trans_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_trans_qry')");
$emp_trans_result = $emp_trans_data->result_array();
$emp_trans_data->next_result();
//employee wise data
$emp_trans_arr = array();
foreach ($emp_trans_result as $key => $arr) {
$emp_trans_arr[$arr['employee_code']] = $arr['monthly_tds'];
}
$off_tds_qry = 'SELECT SUM(amount) as amount,employee_code,category from cw_offline_tds_entry where trans_status = 1 and date_format(str_to_date(concat("01-",payroll_month), "%d-%m-%Y") , "%Y-%m-%d") BETWEEN date_format("'.$this->financial_info[0]->start_date.'", "%Y-%m-%d") and date_format("'.$this->financial_info[0]->end_date.'", "%Y-%m-%d") group by employee_code';
//echo $off_tds_qry; die;
$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();
$off_tds_result_array = array();
if($off_tds_result){
foreach($off_tds_result as $arr){
$off_tds_result_array[$arr['employee_code']] = $arr['amount'];
}
}
//Get previous Employer Tax
$prev_tax_qry = 'select employee_code,previous_tax from cw_previous_company_income where cw_previous_company_income.trans_status =1 and cw_previous_company_income.financial_setting_id = "'.$fin_id.'" and previous_tax > 0';
$prev_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$prev_tax_qry')");
$prev_tax_result = $prev_tax_data->result_array();
$prev_tax_data->next_result();
//employee wise data
$prev_tax_arr = array();
foreach ($prev_tax_result as $key => $arr) {
$prev_tax_arr[$arr['employee_code']] = $arr['previous_tax'];
}
$form24_temp_data_qry = 'select * from cw_form24q_template where cw_form24q_template.trans_status =1';
$form24_temp_data = $this->db->query("CALL sp_a_run ('SELECT','$form24_temp_data_qry')");
$form24_temp_result = $form24_temp_data->result();
$form24_temp_data->next_result();
$form24_col = array();
$map_qry = "";
foreach($form24_temp_result as $form24_rslt){
$form24q_key = $form24_rslt->form24q_key;
$form24q_mapping = $form24_rslt->form24q_mapping;
$form24q_value = $form24_rslt->form24q_value;
$form24q_value = str_replace("+", "+a.", $form24q_value);
$form24q_value = str_replace("(", "(a.", $form24q_value);
if((int)$form24q_mapping > 0){
$form24_col[$form24q_mapping] = $form24q_key;
if($form24q_value){
$map_qry .= "a.".$form24q_value." as ".$form24q_key.",";
}
}
}
if(!$map_qry){
echo json_encode(array('success' => False, 'msg' => "Form 24q Template not Mapped..!"));
exit(0);
}
$process_month_dt = "01-".$end_month;
$emp_tax_data_qry = 'SELECT '.$map_qry.'a.emp_code,emp_name,pan_number,termination_status,last_working_date,IF(a.income_tax_type = 1,"OLD Regime","New Regime") as tax_type FROM cw_tax_calculation as a inner join cw_employees on cw_employees.employee_code = a.emp_code INNER JOIN (SELECT emp_code, date_format(MAX(str_to_date(CONCAT("01-", cw_tax_calculation.process_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_tax_calculation WHERE cw_tax_calculation.trans_status = 1 '.$cond_query.' and fin_set_id = "'.$fin_id.'" and date_format(str_to_date(CONCAT("01-", cw_tax_calculation.process_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") and cw_tax_calculation.trans_status = 1 GROUP BY emp_code) as groupedtt ON a.emp_code = groupedtt.emp_code AND a.process_month = groupedtt.max_date and a.trans_status = 1 and fin_set_id = "'.$fin_id.'"';
$emp_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_tax_data_qry')");
$emp_tax_result = $emp_tax_data->result_array();
$emp_tax_data->next_result();
//employee wise data
$emp_tax_arr = array();
foreach ($emp_tax_result as $key => $arr) {
$emp_tax_arr[$arr['emp_code']] = $arr;
}
$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_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") ' .$dec_query. ' GROUP BY emp_code) as groupedtt ON a.emp_code = groupedtt.emp_code AND a.effective_month = groupedtt.max_date ';
$section_dec_data = $this->db->query("CALL sp_a_run ('SELECT','$section_dec_query')");
$section_dec_result = $section_dec_data->result_array();
$section_dec_data->next_result();
$section_dec_array = array();
foreach($section_dec_result as $arr){
$section_dec_array[$arr['emp_code']] = $arr['pan_card_no'];
}
//print_r($emp_tax_result); die;
$j = 5;
$i = 1;
foreach($emp_tax_arr as $emp_code => $emp_tax_rslt){
$emp_code = $emp_tax_rslt['emp_code'];
$emp_name = $emp_tax_rslt['emp_name'];
$pan_number = $emp_tax_rslt['pan_number'];
$slab_type = $emp_tax_rslt['slab_type'];
$emp_status = $emp_tax_rslt['termination_status'];
$resignation_date = $emp_tax_rslt['last_working_date'];
$tax_amt = $emp_tax_rslt['tax_amt'];
$tax_type = $emp_tax_rslt['tax_type'];
//$tax_deducted= $emp_tax_rslt['tax_deducted'];
$offline_tds = $off_tds_result_array[$emp_code];
$prev_tax = $prev_tax_arr[$emp_code];
if(!$prev_tax){
$prev_tax = 0;
}
$l_pan_number = $section_dec_array[$emp_code];
$total_ded = $emp_trans_arr[$emp_code]+$offline_tds;
//$total_ded = $tax_deducted-$prev_tax;
//$shortfall = $tax_amt - $total_ded;
//echo "BSK $emp_code :: $prev_tax :: $tax_deducted :: $final_tds :: $total_ded <br/>";
if((int)$emp_status === 1){
if($resignation_date === "" || $resignation_date === "0000-00-00"){
$end_date = "";
}else
if($resignation_date <= $this->financial_info[0]->end_date){
$end_date = date('d/m/Y', strtotime($resignation_date));
}else{
$end_date = date('d/m/Y', strtotime($this->financial_info[0]->end_date));
}
}else{
$end_date = date('d/m/Y', strtotime($this->financial_info[0]->end_date));
}
$financial_date = $start_date."-".$end_date;
if((int)$slab_type === 3){
$slab_type_val = "SSC";
}else
if((int)$slab_type === 2){
$slab_type_val = "SC";
}else{
$slab_type_val = "O";
}
$excel2->getActiveSheet()->setCellValue("A$j", "$i").";";//default serial number is fixed in excel column.
$excel2->getActiveSheet()->setCellValue("B$j", "$pan_number").";";//default pan number is fixed in excel column.
$excel2->getActiveSheet()->setCellValue("C$j", "$emp_name").";";//default employee name is fixed in excel column.
$excel2->getActiveSheet()->setCellValue("D$j", "$slab_type_val").";";//default slab type is fixed in excel column.
$excel2->getActiveSheet()->setCellValue("E$j", "$financial_date").";";//default financial setting date is fixed in excel column.
$tax_value = 0;
foreach($form24_col as $form24_key => $form24_val){
$head_val = $head_arr['('.$form24_key.')'];
$tax_value = $emp_tax_rslt[$form24_val];
$excel2->getActiveSheet()->setCellValue("$head_val$j", "$tax_value")->getStyle("$head_val$j")->getNumberFormat().";";
}
$excel2->getActiveSheet()->setCellValue("AW$j", "$emp_code").";";//default employee code is
$excel2->getActiveSheet()->setCellValue("AX$j", "$emp_status").";";//emp status
$excel2->getActiveSheet()->setCellValue("AY$j", "$tax_type").";";//tax type
$excel2->getActiveSheet()->setCellValue("P$j", "$l_pan_number"); //landlord pan
$excel2->setActiveSheetIndex(0)->setCellValue("R$j", "=K$j+L$j+M$j+N$j+O$j+Q$j"); //sec10
$excel2->setActiveSheetIndex(0)->setCellValue("V$j", "=(I$j+J$j)-(R$j+S$j+T$j+U$j)");//sec16
$excel2->setActiveSheetIndex(0)->setCellValue("Z$j", "=V$j+(-W$j)+Y$j"); //total income
$excel2->setActiveSheetIndex(0)->setCellValue("AK$j", "=AA$j+AB$j+AC$j+AD$j+AE$j+AF$j+AG$j+AH$j+AI$j+AJ$j"); //sec 6A
$excel2->setActiveSheetIndex(0)->setCellValue("AL$j", "=Z$j-AK$j");
$excel2->setActiveSheetIndex(0)->setCellValue("AR$j", "=(AM$j+AO$j+AP$j)-(AN$j+AQ$j)");
$excel2->getActiveSheet()->setCellValue("AS$j", "$total_ded")->getStyle("AS$j")->getNumberFormat().";";
$excel2->getActiveSheet()->setCellValue("AT$j", "$prev_tax")->getStyle("AT$j")->getNumberFormat().";";
$excel2->setActiveSheetIndex(0)->setCellValue("AU$j", "=AS$j+AT$j");
//$excel2->setActiveSheetIndex(0)->setCellValue("AU$j", "=AS$j+AT$j");
$excel2->setActiveSheetIndex(0)->setCellValue("AV$j", "=AR$j-AU$j");
$j++;
$i++;
}
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($excel2, 'Excel5');
$objWriter->save("./excel_write/FORM24QA_$fin_id.xls");//dynamic company
$excel_path = "./excel_write/FORM24QA_$fin_id.xls";
return $excel_path;
}
//ABC generator for excel column values
public function getNameFromNumber($num){
$numeric = $num % 26;
$letter = chr(65 + $numeric);
$num2 = intval($num / 26);
if ($num2 > 0) {
return $this->getNameFromNumber($num2 - 1) . $letter;
} else {
return $letter;
}
}
//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' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','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));
}
}
?>