File: /home/cafsindia/uds.cafsinfotech.in/Dump/ZRM/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");
$inactive_emp = $this->input->post("inactive_emp"); // [MS 22-07-2024]
$process_role = implode(',', $this->input->post('process_role'));
$process_month = $this->input->post("process_month");
$pro_mon_frmt_start = date('Y-m-d',strtotime("01-$process_month"));
$pro_mon_frmt_end = date('Y-m-d',strtotime("31-$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;
//GET Employee QUERY Who Active current Fin Year
if($inactive_emp === '1'){
$employee_qry = 'select employee_code,emp_name,role from cw_employees where cw_employees.termination_status = "'.$inactive_emp.'" and last_working_date BETWEEN "'.$pro_mon_frmt_start.'" AND "'.$pro_mon_frmt_end.'"';
}else{
$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();
$inactive_emp_codes = '';
foreach($employee_arr as $arr){
$employee_rslt[$arr['employee_code']] = $arr;
$inactive_emp_code = $arr['employee_code'];
$inactive_emp_codes .= "$inactive_emp_code,";// [MS 22-07-2024]
}
//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
if($inactive_emp === '1'){ // [MS 22-07-2024]
if($inactive_emp_codes){
$inactive_emp_codes = rtrim($inactive_emp_codes, ",");
$type_wise_qry .= ' and cw_tax_calculation.emp_code in ('.$inactive_emp_codes.')';
}
}
$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'];
}
// $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';
// $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'];
// }
// }
// $other_inc_entry_qry = 'select emp_code,'.$other_income_column.' from cw_other_income_entry where effective_month = "'.$process_month.'" and financial_setting_id = "'.$fin_id.'" and trans_status=1';
// $other_inc_info = $this->db->query("CALL sp_a_run ('SELECT','$other_inc_entry_qry')");
// $other_inc_entry_rslt = $other_inc_info->result_array();
// $other_inc_info->next_result();
//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){
foreach($subsec as $key => $value){
$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";
}
$i++;
}
$section_rslt_key = "";
if($section_arr){
$section_rslt_key = ','.implode(',', $section_arr);
}
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);
//,'.$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");
// $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($sec_limit_arr[$col_label]){ will enable if req
// if($total_earn >= $sec_limit_arr[$col_label] && $sec_limit_arr[$col_label] > 0){
// $total_earn = $sec_limit_arr[$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)){
//echo "BSK1 $col_label <br/>";
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];
}
//echo "BSK $tot_col_label :: $col_view_name :: $col_label <br/>";
$total_earn = $emp_info[$tot_col_label];
if(!$total_earn){
$total_earn = "0";
}
}
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_tax_amtlabel === "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];
//echo "BSK $col_label :: $col_view_name :: $total_earn <br/>";
}
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];
}
$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 = 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_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;
}
}
?>