File: /home/cafsindia/hrms_allyindian_com/application_bk/controllers/Pt_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Pt_report extends Action_controller{
public function __construct(){
parent::__construct('pt_report');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$location_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_professional_tax_location` where trans_status = 1')");
$location_result = $location_info->result();
$location_info->next_result();
$location[""] = "---- Select Location ----";
foreach($location_result as $for){
$location_id = $for->prime_professional_tax_location_id;
$location_name = $for->professional_tax_location;
$location[$location_id] = $location_name;
}
$data['location'] = $location;
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
public function pt_save(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'msg' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$location_name = $this->input->post('location_name');
$process_month = $this->input->post('process_month');
$location = (int)$this->input->post('location');
$report_model = (int)$this->input->post('report_model');
$today_date = date("d-m-Y H:i:s");
$pt_column_qry = 'SELECT count(*) as pt_column_count FROM cw_payroll_function_map WHERE loc_name in("pt_gross","professional_tax_amount") and trans_status = 1';
$pt_column_info = $this->db->query("CALL sp_a_run ('SELECT','$pt_column_qry')");
$pt_column_rslt = $pt_column_info->result();
$pt_column_info->next_result();
$pt_column_count = $pt_column_rslt[0]->pt_column_count;
if((int)$pt_column_count !== 2){
echo json_encode(array('success' => false, 'msg' => 'Please add payroll function mapping for PT Gross ,Professional Tax Amount'));
exit(0);
}
$company = 'select company_name,address from cw_company_information where cw_company_information.trans_status = 1';
$comp_info = $this->db->query("CALL sp_a_run ('SELECT','$company')");
$comp_result = $comp_info->row();
$comp_info->next_result();
$company_name = $comp_result->company_name;
$address = $comp_result->address;
$pt_common_qry ='SELECT GROUP_CONCAT(IF(cw_form_setting.transaction_type IN (1,2,3),CONCAT("cw_transactions.", db_column, " AS ", loc_name),CONCAT("cw_employees.", db_column, " AS ", loc_name))) AS select_qry,GROUP_CONCAT(IF(cw_form_setting.transaction_type IN (1,2,3),CONCAT("IFNULL(SUM(cw_transactions.", db_column, "), 0) AS ", loc_name),CONCAT("IFNULL(SUM(cw_employees.", db_column, "), 0) AS ", loc_name))) AS sum_select_qry FROM cw_payroll_function_map JOIN cw_form_setting ON cw_payroll_function_map.db_column = cw_form_setting.label_name WHERE cw_payroll_function_map.trans_status = 1';
// $loc_column_qry = 'select group_concat("cw_transactions.",db_column," as ",loc_name) as select_qry from cw_payroll_function_map where trans_status = 1';
$pt_common_info = $this->db->query("CALL sp_a_run ('SELECT','$pt_common_qry')");
$pt_common_rslt = $pt_common_info->result_array();
$pt_common_info->next_result();
$select_qry = $pt_common_rslt[0]['select_qry'];
if(!$select_qry){
echo json_encode(array('success'=>FALSE,'msg'=>'Column Not Mapped....'));
exit(0);
}
if($report_model === 1){
$qry = 'select cw_employees.employee_code,cw_employees.emp_name,cw_transactions.net_pay,'.$select_qry.' from cw_transactions inner join cw_employees on cw_employees.employee_code = cw_transactions.employee_code where transactions_month ="'.$process_month.'" and cw_transactions.professional_tax_location ="'.$location.'" and cw_transactions.trans_status = 1';
$qry_info = $this->db->query("CALL sp_a_run ('SELECT','$qry')");
$qry_result = $qry_info->result();
$qry_info->next_result();
$total_employees = count($qry_result);
$tr_line = "";
$total_pay = 0;
$total_pt = 0;
foreach($qry_result as $rslt){
$code = $rslt->employee_code;
$name = $rslt->emp_name;
$pt_gross = $rslt->pt_gross;
$professional_tax = $rslt->professional_tax_amount;
$total_pay = $total_pay + $pt_gross;
$total_pt = $total_pt + $professional_tax;
$tr_line .= "<tr><td>$code</td><td>$name</td><td>$pt_gross</td><td>$professional_tax</td></tr>";
}
$total_pay = number_format((float)$total_pay, 2, '.', '');
$total_pt = number_format((float)$total_pt, 2, '.', '');
if($employee_code == '' && $name == ''){
$tr_line .= "<tr style='text-align:center;font-weight:bold;'><td colspan='6'>No Data Found!!</td></tr>";
}else{
$tr_line .= "<tr style='text-align:center;font-weight:bold;'><td>Total Employees :$total_employees</td><td>Total</td><td>$total_pay</td><td>$total_pt</td></tr>";
}
$table_data = "<div style='width:700px; margin-left: auto; margin-right: auto; padding: 15px; background-color: #EEEEEE; border-radius: 3px; box-shadow: 0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2);'>
<table style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;text-align: left;font-size: 14px;background-color: #FFFFFF;' border='1' cellspacing='0' cellpadding='4'>
<tr>
<td colspan='2'><h3 style='text-align:center;'>PROFESSIONAL TAX REPORT</h3></td>
</tr>
<tr>
<td colspan='2' style='border-bottom:none !important;'>
<h3 style='color:#d3434d;margin:10px;font-size:21px;font-weight:bold;text-align:center;'>".$company_name."
</h3>
</td>
</tr>
<tr>
<td style='border-right:none !important;border-top:none !important;'>
<h3 style='color:#d3434d;margin:10px;font-size:16px;font-weight:bold;'>Professional Tax for the Month of ".strtoupper(date("M-Y",strtotime("01-".$this->input->post('process_month'))))."- Location -".$location_name."</h3>
</td>
<td style='text-align:center;font-weight:bold;border-top:none !important;border-left:none !important;'>".$today_date."</td>
</tr>
</table>
<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'>
<thead>
</thead>
<tbody>
<tr style='background-color:#f2f2f2;color: #000000;font-weight:bold;'>
<th>Code</th>
<th>Name</th>
<th>Gross</th>
<th>Profession Tax</th>
</tr>
$tr_line
</tbody>
</table>
</div>";
$table_data = "<!DOCTYPE html><html> <body>".$table_data."</body></html>";
}else
if($report_model === 2){
//inner join cw_professional_tax_tax_range on prime_professional_tax_id=professional_tax_location
$qry = 'select '.$select_qry.',count(employees_id) as total_employees,MIN(cw_professional_tax_tax_range.earning_range_from)as earning_range_from,MAX(cw_professional_tax_tax_range.earning_range_to) as earning_range_to from cw_transactions inner join cw_employees on cw_employees.employee_code = cw_transactions.employee_code inner join cw_professional_tax_tax_range on cw_professional_tax_tax_range.prime_professional_tax_id=cw_transactions.professional_tax_location WHERE transactions_month ="'.$process_month.'" and cw_transactions.professional_tax_location ="'.$location.'" and cw_transactions.trans_status = 1 GROUP by cw_transactions.professional_tax_location ';
$stat_info = $this->db->query("CALL sp_a_run ('SELECT','$qry')");
$stat_result = $stat_info->result();
$stat_info->next_result();
$total_prof = 0;
foreach($stat_result as $stat){
$earning_range_from = $stat->earning_range_from;
$earning_range_to = $stat->earning_range_to;
$total_employees = $stat->total_employees;
$professional_tax = $stat->professional_tax_amount;
$tax_amt = $total_employees * $professional_tax;
$tax_amt = number_format((float)$tax_amt, 2, '.', '');
$total_prof = $total_prof + $tax_amt;
$stat_line .= "<tr><td>$earning_range_from - $earning_range_to</td><td>$total_employees</td><td>$professional_tax</td><td>$tax_amt</td></tr>";
}
$total_prof = number_format((float)$total_prof, 2, '.', '');
if($total_employees == ''){
$stat_line .= "<tr style='text-align:center;font-weight:bold;'><td colspan='4'>No Data Found!!</td></tr>";
}else{
$stat_line .= "<tr style='text-align:center;font-weight:bold;'><td colspan='3' style='text-align:right'>TotalRs.</td><td>$total_prof</td></tr>";
}
$table_data = "<div style='width:700px; margin-left: auto; margin-right: auto; padding: 0px;background-color: #EEEEEE; border-radius: 3px; box-shadow: 0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgb(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2);'>
<table style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border:0px;text-align: left;font-size: 14px;background-color: #FFFFFF;' border='1' cellspacing='0' cellpadding='4'>
<tr>
<td colspan='2'><h3 style='text-align:center;'>(See Rule 12)</h3>
<p><h3 style='text-align:center;'>RETURNS OF TAX PAYABLE BY EMPLOYER </h3></p>
<h4 style='margin-left:20px;'>Under Sub - Section (1) of Section 7 of the ".$location_name." Tax on <br>Professions,Trades,Callings and Employments Act,1987</h4>
<h4 style='color:#d3434d;margin:10px;font-size:16px;font-weight:bold;'>Return of tax payable for the monthending on ".strtoupper(date("M-Y",strtotime("01-".$this->input->post('process_month'))))."</h4>
</td>
</tr>
<tr><td><h4>Name Of The Employer:</h4></td><td><h4 style='color:#d3434d;margin:10px;font-size:21pxfont-weight:bold;text-align:center;'>".$company_name."</h4></td></tr>
<tr><td colspan='2'><h4>Address: ".$address."</h4></td></tr>
<tr><td colspan='2'><h4>Registration Certificate No.</h4>
<h4>Number of employees during the month in respect of whom the tax is payable is as under:</h4></td></tr>
</table>
<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'>
<thead>
</thead>
<tbody>
<tr style='background-color:#f2f2f2;color: #000000;font-weight:bold;'>
<th>Employees Whose Monthly Salaries Or wages or both are</th>
<th>Number Of Employees</th>
<th>Rate Of Tax Per Month Rs.</th>
<th>Amount Of Tax Deducted Rs.</th>
</tr>
$stat_line
<tr><td colspan='3'><h5>Add: Simple Interest payable (if any) on the above amount at two percent per month or part there of(vide Section 11 of the Act)</h5></td><td></td></tr>
<tr><td colspan='3' style='text-align:right'>Grand Total</td><td>$total_prof</td></tr><tr><td>Amount Paid: $total_prof</td><td colspan='2'> Under challan no.</td><td>dated:</td></tr>
<tr><td colspan='4'>I Certify that all the employees who are liable to pay the tax in my employ during the period of return have been covered by the forgoing particulars..I also certify that the necessary revision in the amount of tax deductible from the salary of wages of the employees on account of variation in the salary or wages earned by team been made whatever necessary.<br/><br/></td></tr>
<tr><td colspan='3' style='border-bottom:none !important;border-right:none !important;'>I, shri</td><td style='border-bottom:none !important;border-left:none !important;'>Solemnly declare that the</td></tr>
<tr><td colspan='4' style='border-top:none !important;'>above statments are true to the best of my knowledge and belief.<br/></td></tr>
<tr><td colspan='2'>Place</td><td colspan='2'>Signature<span style='color:#bbbbbb;margin-left:70px;'>(Employer)</span></td></tr>
<tr><td colspan='2'>Date</td><td colspan='2'>Status</td></tr>
</tbody>
</table>
</div>";
$table_data = "<!DOCTYPE html><html> <body>".$table_data."</body></html>";
}
//$combine_data .= $table_data; //For Combine all pdf
$folder = $this->pt_generate_pdf($table_data,$location_name,$process_month,$location,$report_model);
echo json_encode(array('success' => TRUE,'folder' => $folder,'process_month' => $process_month));
}
public function pt_generate_pdf($table_data,$location_name,$process_month,$location,$report_model){
$location_name = strtolower(str_replace(" ","_",$location_name));
if($report_model == 1){
$model_name = "Detail";
}else
if($report_model == 2){
$model_name = "Statutory";
}
$set_paper = "portrait";
$folder = "ptax_report/".$process_month."/".$model_name;
$path = $this->common_generate_pdf($table_data,$folder,$location_name,$set_paper,'A4','','');
return $path;
}
}
?>