File: /home/cafsindia/cloud_cafsinfotech_in/application/controllers/Detailed_worksheet.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Detailed_worksheet extends Action_controller{
public function __construct(){
parent::__construct('detailed_worksheet');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$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(){
$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(){
$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");
//GET Employee QUERY
$employee_qry = 'select employee_code,emp_name,role from cw_employees where cw_employees.trans_status = 1';
$employee_info = $this->db->query("CALL sp_a_run ('SELECT','$employee_qry')");
$employee_rslt = $employee_info->result_array();
$employee_info->next_result();
$employee_rslt = array_reduce($employee_rslt, function($result, $arr){
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
//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();
$category_rslt = array_reduce($category_rslt, function($result, $arr){
$result[$arr['prime_category_id']] = $arr['category_name'];
return $result;
}, array());
if((int)$process_type === 1){
$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 where cw_tax_calculation.trans_status = 1 and cw_tax_calculation.emp_code != "0001" 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 = $emp_data->result_array();
$emp_data->next_result();
$emp_rslt = array_reduce($emp_rslt, function($result, $arr){
$result[$arr['emp_code']] = $arr;
return $result;
}, array());
if(!$emp_rslt){
echo json_encode(array('success'=>FALSE,'message'=>"No Data Found"));
exit(0);
}
//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 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();
$earnings_result = array_reduce($earnings_rslt, function($result, $arr){
$result[$arr['earnings']] = $arr['view_name'];
return $result;
}, array());
$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 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';
$section_info = $this->db->query("CALL sp_a_run ('SELECT','$section_qry')");
$section_rslt = $section_info->result_array();
$section_info->next_result();
$section_result = array_reduce($section_rslt, function($result, $arr){
$result[$arr['tax_subsection_column']] = $arr['tax_act_details'];
return $result;
}, array());
$sub_section_result = array_reduce($section_rslt, function($result, $arr){
$result[$arr['tax_section_column']] = $arr['tax_section'];
return $result;
}, array());
$section_val_arr = array_column($section_rslt, 'tax_subsection_column');
$section_rslt_arr = array_column($section_rslt,'tax_section_column', 'tax_subsection_column');
$sub_arr = array_unique(array_column($section_rslt, 'tax_section_column'));
$i = 0;
foreach ($section_rslt_arr as $key => $value) {
if(in_array($value, $sub_arr)){
$sec_key = $value;
if($sec_key_inc === $sec_key){
$section_arr[$i] = $key;
}else{
$section_arr[$i] = $key;
if($sec_key_inc){
$i = $i+1;
$section_arr[$i] = $sec_key_inc;
$i = $i+1;
$section_arr[$i] = 'tot_gross_'.$sec_key_inc;
}
}
}
$sec_key_inc = $sec_key;
$i++;
}
$section_arr[$i] = $sec_key_inc;
$i = $i+1;
$section_arr[$i] = 'tot_gross_'.$sec_key_inc;
$section_rslt_key = "";
if($section_arr){
$section_rslt_key = ','.implode(',', $section_arr);
}
//require_once APPPATH."/third_party/PHPExcel.php";
require_once APPPATH."/controllers/php_excel/PHPExcel.php";
$objPHPExcel = new PHPExcel();
$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$oldmask = umask(0);
if (!file_exists($create_folder)){
mkdir($create_folder, 0777, true);
}
umask($oldmask);
$writer->save($create_folder.'/'.$create_file.'.xls');
$hign_col_num = 3;
$filename = dirname(__FILE__)."/php_excel/PHPExcel/IOFactory.php";
// include($filename);
$excel2 = PHPExcel_IOFactory::createReader('Excel5');
$excel2 = $excel2->load($create_folder.'/'.$create_file.'.xls');//default excel template
$excel_sheet = $excel2->setActiveSheetIndex(0);
$earnings_column = 'emp_code,emp_name'.$earnings_rslt_key.',gross_17a,gross_17b,gross_17c,17_cross_toal'.$section_rslt_key.',surcharge_charge,rebate_amt,edu_cess,total_tax_on_income,final_tax_pay,tax_deducted,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','surcharge_charge'=>'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_cross_toal'=>'Gross Total[17(1),17(2),17(3)]');
// $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");
$j = 6;
foreach($emp_rslt as $key => $emp_info){
$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, $section_val_arr)){
$total_earn = $emp_info[$col_label];
if(!$total_earn){
$total_earn = "0";
}
$col_view_name = $section_result[$col_label];
}
if(in_array($col_label, $sub_arr)){
if('tot_gross_'.$col_label === $get_label){
$tot_col_label = 'gross_tot_'.$col_label;
$col_view_name = "Gross After Exemption";
}else{
$tot_col_label = 'tot_'.$col_label;
$col_view_name = $sub_section_result[$col_label];
}
$total_earn = $emp_info[$tot_col_label];
if(!$total_earn){
$total_earn = "0";
}
}
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_pay" || $col_label === "tax_deducted" || $col_label === "final_tax_amt" || $col_label === "surcharge_charge"){
$total_earn = $emp_info[$col_label];
if(!$total_earn){
$total_earn = "0";
}
$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_cross_toal"){
$total_earn = $emp_info["gross_17a"]+$emp_info["gross_17b"]+$emp_info["gross_17c"];
if(!$total_earn){
$total_earn = "0";
}
$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 );
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 = PHPExcel_IOFactory::createWriter($excel2, 'Excel5');
$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_role,$process_emp_id,$category_rslt,$employee_rslt);
}
public function view_table_data($excel_path,$process_type,$process_role,$process_emp_id,$category_rslt,$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;
}
}
?>