File: /home/cafsindia/cpaqua.cafsinfotech.in/application/controllers/Detailed_worksheet.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
require('./application/libraries/PHPSpreadsheet/autoload.php');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Detailed_worksheet extends Action_controller{
public function __construct(){
parent::__construct('detailed_worksheet');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['key'] = $this->generateKey();
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT prime_category_id,category_name FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$prime_category_id = array_column($role_result, 'prime_category_id');
$category_name = array_column($role_result, 'category_name');
$role_list = array_combine( $prime_category_id, $category_name);
$data['process_role'] = $role_list;
$this->load->view("$this->control_name/manage",$data);
}
//Emp Auto Complete
public function emp_suggest(){
$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_get('term');
$final_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 and employee_code like "'.$search_term.'%"';
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$suggestions[] = array('value' => $employee_code, 'label' => "$employee_code - $emp_name");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
public function form_generate(){
$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_type = $this->input->post("process_type");
$process_emp_id = $this->input->post("process_emp_id");
$process_role = implode(',', $this->input->post('process_role'));
$process_month = $this->input->post("process_month");
$fin_id = $this->financial_info[0]->prime_financial_setting_id;
$start_date = $this->financial_info[0]->start_date;
$end_date = $this->financial_info[0]->end_date;
if(!$fin_id){
echo json_encode(array('success' => false,'message' => 'Financial Setting Not Done..','table_data' => ""));
exit(0);
}
//GET Employee QUERY Who Active current Fin Year
$employee_qry = 'select employee_code,emp_name,role from cw_employees where cw_employees.trans_status = 1 and (termination_status = 0 and date_of_joining <= "'.$end_date.'" or termination_status = 1 and last_working_date >= "'.$start_date.'")';
$employee_info = $this->db->query("CALL sp_a_run ('SELECT','$employee_qry')");
$employee_arr = $employee_info->result_array();
$employee_info->next_result();
//Generate Multi dimentional Array
$employee_rslt = array();
foreach($employee_arr as $arr){
$employee_rslt[$arr['employee_code']] = $arr;
}
//Get Category
$category_qry = 'select prime_category_id,category_name from cw_category where cw_category.trans_status = 1';
$category_info = $this->db->query("CALL sp_a_run ('SELECT','$category_qry')");
$category_rslt = $category_info->result_array();
$category_info->next_result();
foreach($category_rslt as $arr){
$category_rslt[$arr['prime_category_id']] = $arr['category_name'];
}
if((int)$process_type === 1){ //Process by
$type_wise_qry = ' and emp_code = "'.$process_emp_id.'"';
$emp_role = $employee_rslt[$process_emp_id]['role'];
$create_folder = './detailed_worksheet/'.$process_month.'/'.$category_rslt[$emp_role];
$create_file = $process_emp_id;
}else
if((int)$process_type === 2){
$type_wise_qry = ' and emp_role in ('.$process_role.')';
$create_folder = './detailed_worksheet/'.$process_month;
$create_file = 'detailed_worksheet';
}
//Get Tax value Query
$emp_qry = 'select * from cw_tax_calculation inner join cw_income_tax_type on cw_income_tax_type.prime_income_tax_type_id = cw_tax_calculation.income_tax_type where fin_set_id = "'.$fin_id.'" and cw_tax_calculation.trans_status = 1 and cw_tax_calculation.process_month = "'.$process_month.'" '.$type_wise_qry.'';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_rslt_arr = $emp_data->result_array();
$emp_data->next_result();
$emp_rslt = array();
foreach($emp_rslt_arr as $arr){
$emp_rslt[$arr['emp_code']] = $arr;
}
if(!$emp_rslt){
echo json_encode(array('success'=>FALSE,'message'=>"No Data Found"));
exit(0);
}
//Get Other Income Columns
$other_inc_qry = 'select other_income_column,income_description,income_type from cw_other_income where trans_status=1';
$other_inc_info = $this->db->query("CALL sp_a_run ('SELECT','$other_inc_qry')");
$other_inc_rslt = $other_inc_info->result_array();
$other_inc_info->next_result();
$oth_inc_columns = array();
$grs_oth_inc_columns = array();
foreach ($other_inc_rslt as $key => $value){
$income_type = (int)$value['income_type'];
// if Gross Income 17(1),17(2),17(3) then this should display with Salary data
if($income_type === 3 || $income_type === 4 || $income_type === 5){
$grs_oth_inc_columns[] = $value['other_income_column'];
}else{
$oth_inc_columns[] = $value['other_income_column'];
}
}
$grs_oth_inc_qrycolumns = implode(",",$grs_oth_inc_columns);
$oth_inc_viewname = array_column($other_inc_rslt,'income_description', 'other_income_column'); // Common for all display
//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'];
}
//get earning components
$earnings_query = 'SELECT earnings,view_name FROM `cw_income_matching` INNER JOIN cw_form_setting on cw_form_setting.label_name = cw_income_matching.earnings WHERE financial_setting_id = "'.$fin_id.'" and cw_income_matching.trans_status = 1 and prime_module_id = "employees" order by order_no';
$earnings_data = $this->db->query("CALL sp_a_run ('SELECT','$earnings_query')");
$earnings_rslt = $earnings_data->result_array();
$earnings_data->next_result();
foreach($earnings_rslt as $arr){
$earnings_result[$arr['earnings']] = $arr['view_name'];
}
$earnings_rslt_arr = array_column($earnings_rslt, 'earnings');
if($earnings_rslt_arr){
$earnings_rslt_key = ','.implode(',', $earnings_rslt_arr);
}
// get section value
$section_qry = 'select tax_subsection_column,tax_act_details,tax_section_column,cw_tax_section.tax_section,tax_subsection_limit from cw_tax_section inner join cw_tax_sub_section on cw_tax_sub_section.tax_section = cw_tax_section.prime_tax_section_id where cw_tax_section.trans_status = 1 and cw_tax_sub_section.trans_status = 1 and cw_tax_sub_section.financial_setting_id = '.$fin_id.' order by tax_section_column,tax_subsection_column';
$section_info = $this->db->query("CALL sp_a_run ('SELECT','$section_qry')");
$section_rslt = $section_info->result_array();
$section_info->next_result();
foreach($section_rslt as $arr){
$section_result[$arr['tax_section_column']][$arr['tax_subsection_column']] = $arr['tax_act_details'];
$section_name_arr[$arr['tax_subsection_column']] = $arr['tax_act_details'];
}
$sec_limit_arr = array();
foreach($section_rslt as $arr){
$sub_section_result[$arr['tax_section_column']] = $arr['tax_section'];
if((int)$arr['tax_section'] !== 3){
$sec_limit_arr[$arr['tax_subsection_column']] = $arr['tax_subsection_limit'];
}
}
$section_val_arr = array_column($section_rslt, 'tax_subsection_column');
$sub_arr = array_unique(array_column($section_rslt, 'tax_section_column'));
$i = 0;
$section_arr = array();
foreach($section_result as $sec_key => $subsec){
// Columns Added By AR
foreach($subsec as $key => $value){
if($key === 'taxsubsec_1'){
$section_arr[$i] = $key;
$section_arr[++$i]= 'pan_card_no';
}else if($key === 'taxsubsec_21'){
$section_arr[$i] = $key;
$section_arr[++$i]= 'lender_pan_card_no';
}else{
$section_arr[$i] = $key;
}
$i++;
}
$tot_key = "tot_".$sec_key;
$section_arr[$i] = $tot_key;
$i = $i+1;
$new_key = 'gross_tot_'.$sec_key;
$section_arr[$i] = $new_key;
if($new_key === 'gross_tot_taxsec_2'){
$i = $i+1;
foreach($oth_inc_columns as $oth_inc_key => $oth_inc_val){
$section_arr[$i] = $oth_inc_val;
$i = $i+1;
}
$section_arr[$i] = "final_gross";
}
if($new_key === 'gross_tot_taxsec_4'){
$section_arr[++$i] = "final_gross";
$section_arr[++$i] = "total_income_round_10";
}
$i++;
}
$section_rslt_key = "";
if($section_arr){
$section_rslt_key = ','.implode(',', $section_arr);
}
$objPHPExcel = new Spreadsheet();
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($objPHPExcel, 'Xls');
$oldmask = umask(0);
if (!file_exists($create_folder)){
mkdir($create_folder, 0777, true);
}
umask($oldmask);
$writer->save($create_folder.'/'.$create_file.'.xls');
$excel2 =\PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');
$excel2 = $excel2->load($create_folder.'/'.$create_file.'.xls');//default excel template
$excel_sheet = $excel2->setActiveSheetIndex(0);
//,'.$other_income_column.'
$earnings_column = 'emp_code,emp_name,income_tax_type'.$earnings_rslt_key.','.$grs_oth_inc_qrycolumns.',gross_17a,gross_17b,gross_17c,17_gross_toal'.$section_rslt_key.',total_tax_on_income,surcharge_amt,rebate_amt,edu_cess,tax_amt,tax_deducted,final_tax_pay,final_tax_amt';
$earnings_column_name = explode(',', $earnings_column);
$static_label = array('rebate_amt'=>'Rebate','edu_cess'=>'Education Cess','total_tax_on_income'=>'Tax on total Income','tax_deducted'=>'Tax Deducted At Source u/s 192(1)','final_tax_pay'=>'Tax Payable / Refundable','final_tax_amt'=>'Per Month Value','emp_code'=>'Employee Code','emp_name'=>'Employee Name','income_tax_type'=>'Income Tax Type','surcharge_amt'=>'Surcharge','tax_amt'=>'Tax Payable and surcharge thereon','gross_17a'=>'Salary as per provisions contained in sec. 17(1)','gross_17b'=>'Value of perquisites u/s 17(2)[as per Form No 12BA]','gross_17c'=>'Profits in lieu of salary under section 17(3)[as per Form No 12BA]','17_gross_toal'=>'Gross Total[17(1),17(2),17(3)]','final_gross'=>"Gross Total Income" ,'pan_card_no'=>'Pan Card No','lender_pan_card_no'=>'Lender Pan Card No','total_income_round_10'=>'Total Income (Round By 10 Rupess)');
// $compay_details = $this->company_info;
$company_name = $this->company_info[0]->company_name;
$company_address = $this->company_info[0]->address;
$company_city = $this->company_info[0]->city.','.$this->company_info[0]->state.','.$this->company_info[0]->country;
$excel2->getActiveSheet()->setCellValue("A1", $company_name)->getStyle("A1");
$excel2->getActiveSheet()->setCellValue("A2", $company_address)->getStyle("A2");
$excel2->getActiveSheet()->setCellValue("A3", $company_city)->getStyle("A3");
$excel2->getActiveSheet()->setCellValue("A4", '')->getStyle("A4");
// Freeze the first three columns
$excel2->getActiveSheet()->freezePane('D1');
$j = 6;
foreach($emp_rslt as $key => $emp_info){
$emp_code = $emp_info["emp_code"];
$i = 0;
foreach ($earnings_column_name as $earn_key => $col_label) {
$get_label = $col_label;
$col_label = str_replace('tot_gross_', '', $col_label);
if(in_array($col_label, $earnings_rslt_arr)){
$proj_label = "proj_".$col_label;
$earn_label = "earn_".$col_label;
$proj_data = $emp_info[$proj_label];
$earn_data = $emp_info[$earn_label];
$total_earn = $earn_data+$proj_data;
if(!$total_earn){
$total_earn = "0";
}
$col_view_name = $earnings_result[$col_label];
}
if(in_array($col_label,$grs_oth_inc_columns)){
$total_earn = $emp_info[$get_label];
if(!$total_earn){
$total_earn = "0";
}
$col_view_name = $oth_inc_viewname[$col_label];
}
if(in_array($col_label, $section_val_arr)){
$total_earn = $emp_info[$col_label];
if(!$total_earn){
$total_earn = "0";
}
$col_view_name = $section_name_arr[$col_label];
}
$get_label = str_replace("gross_tot_","",$get_label);
$get_label = str_replace("tot_","",$get_label);
if(in_array($get_label, $sub_arr)){
if('gross_tot_'.$get_label === $col_label){
$tot_col_label = 'gross_tot_'.$get_label;
$col_view_name = "Gross After Exemption";
}else{
$tot_col_label = 'tot_'.$get_label;
$col_view_name = "Total ".$sub_section_result[$get_label];
}
$total_earn = $emp_info[$tot_col_label];
if(!$total_earn){
$total_earn = "0";
}
}
// Round the Total value By AR
if($col_label === "total_income_round_10"){
$gross_total = $emp_info['final_gross'];
$total_earn = ceil($gross_total / 10) * 10;
$col_view_name = $static_label[$col_label];
}
if(in_array($col_label,$oth_inc_columns)){
$total_earn = $emp_info[$get_label];
if(!$total_earn){
$total_earn = "0";
}
$col_view_name = $oth_inc_viewname[$col_label];
}
//|| $col_label === "tax_amt"
if($col_label === "emp_code" || $col_label === "gross_17a" || $col_label === "gross_17b" || $col_label === "gross_17c" || $col_label === "rebate_amt" || $col_label === "edu_cess" || $col_label === "total_tax_on_income" || $col_label === "final_tax_amt" || $col_label === "tax_amt" || $col_label === "surcharge_amt" || $col_label === "final_gross" || $col_label === "" ){
$total_earn = $emp_info[$col_label];
if(!$total_earn){
$total_earn = "0";
}
$col_view_name = $static_label[$col_label];
}
if($col_label === "tax_deducted"){
//$offline_tds = $off_tds_result_array[$emp_code]; +$offline_tds
$total_ded = $emp_trans_arr[$emp_code];
$total_earn = $total_ded;
$col_view_name = $static_label[$col_label];
}
if($col_label === "final_tax_pay"){
$tax_amt = $emp_info["tax_amt"];
//$offline_tds = $off_tds_result_array[$emp_code]; +$offline_tds
$total_ded = $emp_trans_arr[$emp_code];
$tax_amt = $tax_amt - $total_ded;
$total_earn = $tax_amt;
$col_view_name = $static_label[$col_label];
}
if($col_label === "emp_name"){
$emp_codes = $emp_info["emp_code"];
$total_earn = $employee_rslt[$emp_codes]['emp_name'];
$col_view_name = $static_label[$col_label];
}
if($col_label === "17_gross_toal"){
//$total_earn = $emp_info["gross_17a"]+$emp_info["gross_17b"]+$emp_info["gross_17c"];
$total_earn = $emp_info["earning_total"];
if(!$total_earn){
$total_earn = "0";
}
$col_view_name = $static_label[$col_label];
}
if($col_label === "income_tax_type"){
$total_earn = $emp_info[$col_label];
$col_view_name = $static_label[$col_label];
}
if($col_label === "pan_card_no"){
$total_earn = $emp_info[$col_label];
$col_view_name = $static_label[$col_label];
}
if($col_label === "lender_pan_card_no"){
$total_earn = $emp_info[$col_label];
$col_view_name = $static_label[$col_label];
}
$excel_col = $this->columnFromIndex($earn_key);
$excel2->getActiveSheet()->setCellValue($excel_col."5", $col_view_name)->getStyle($excel_col."5")->getFont()->setBold( true );
$excel2->getActiveSheet()->freezePane('D' . ("5" + 1)); // row header freez
if($excel_col === 'A'){
$excel2->getActiveSheet()->setCellValue("$excel_col$j", $total_earn.' ')->getStyle("$excel_col$j");
}else{
$excel2->getActiveSheet()->setCellValue("$excel_col$j", $total_earn)->getStyle("$excel_col$j");
}
}
$i++;
$j++;
}
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($excel2, 'Xls');
$objWriter->save($create_folder.'/'.$create_file.'.xls');//dynamic company
$excel_path = $create_folder.'/'.$create_file.'.xls';
$this->view_table_data($excel_path,$process_type,$process_emp_id,$employee_rslt);
}
public function view_table_data($excel_path,$process_type,$process_emp_id,$employee_rslt){
if((int)$process_type === 1){
$tbl_head = "<th>Employee Code</th><th>Employee Name</th><th>Option</th>";
$tble_line = "<td>$process_emp_id</td><td>".$employee_rslt[$process_emp_id]['emp_name']."</td>";
}else
if((int)$process_type === 2){
$tbl_head = "<th>Category Name</th><th>Option</th>";
$tble_line = "<td>Detailed Worksheet</td>";
}
$tble_line .= "<td><a class='btn-primary btn-sm' href='$excel_path'><span class='fa fa-download' > </span> Download </a></td>";
$table_data = "<table class='table table-striped table-bordered' id='emp_details'>
<thead>
<tr>
$tbl_head
</tr>
</thead>
<tbody>
$tble_line
</tbody>
</table>";
echo json_encode(array('success'=>TRUE,'message'=>"",'table_data' => $table_data));
}
function columnFromIndex($number){
if($number === 0)
return "A";
$name='';
while($number>0){
$name=chr(65+$number%26).$name;
$number=intval($number/26)-1;
if($number === 0){
$name="A".$name;
break;
}
}
return $name;
}
}
?>