File: /home/cafsindia/uds.cafsinfotech.in/application/controllers/Pdf_generation.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
require_once ('./application/libraries/PDFMerger.php');
use PDFMerger\PDFMerger;
define('DEBUG_TCPDF', true);
class Pdf_generation extends Action_controller{
public function __construct(){
parent::__construct('pdf_generation');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//PERSONAL CODE
$per_area_rslt = $this->query_build_function('personal_code,personal_name','cw_sap_personal_area','','trans_status = 1 and FIND_IN_SET(personal_code, "'.$this->logged_area_access.'") ');
$per_area_list[''] = "---- Select Perosnal Area----";
foreach($per_area_rslt as $for){
$personal_code = $for['personal_code'];
$personal_name = $for['personal_name'];
$per_area_list[$personal_code] = $personal_code.' - '.$personal_name;
}
$data['per_area_list'] = $per_area_list;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//FETCH PROJECT ID FROM PER AREA
public function pro_id_fetch(){
$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);
}
$personal_code = $this->input->post("personal_code");
$project_qry = 'SELECT prime_sap_project_id,pro_id,pro_desc FROM cw_sap_project WHERE trans_status = 1 AND pro_personal_area_id = "'.$personal_code.'" ';
$project_info = $this->db->query("CALL sp_a_run ('SELECT','$project_qry')");
$project_rslt = $project_info->result_array();
$project_info->next_result();
$project_id[''] = "---- Select Project----";
foreach($project_rslt as $for){
$prime_id = $for['prime_sap_project_id'];
$pro_id = $for['pro_id'];
$pro_desc = $for['pro_desc'];
$project_id[$pro_id] = $pro_id.' - '.$pro_desc;
}
if($project_rslt){
echo json_encode(array('success' => true,'column_rslt' => $project_id));
}else{
echo json_encode(array('success' => false,'message' => 'No Record Found', 'column_rslt' => ""));
}
}
//FETCH WBS ELEMENT
public function wbs_element_fetch(){
$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_month = $this->input->post("process_month");
$personal_code = $this->input->post("personal_code");
$project_id = $this->input->post("project_id");
$start_date = date("Y-m-t",strtotime('01-'.$process_month));
$end_date = date("Y-m-d",strtotime('01-'.$process_month));
//AND cw_transactions_fms.sap_trans_status IN (2)
$wbs_qry = 'SELECT cw_sap_wbs.wbs_id,CONCAT_WS(" ~ ", wbs_id,wbs_desc) as wbs_desc FROM cw_sap_wbs INNER JOIN cw_transactions_fms ON cw_transactions_fms.wbs_element = cw_sap_wbs.wbs_id WHERE cw_sap_wbs.wbs_personal_area_id = "'.$personal_code.'" AND cw_sap_wbs.wbs_project_id = "'.$project_id.'" AND cw_sap_wbs.trans_status = 1 AND cw_transactions_fms.trans_status = 1 AND cw_sap_wbs.payslip_status = "1" AND cw_sap_wbs.wbs_start_date <= "'.$start_date.'" AND cw_sap_wbs.wbs_end_date >= "'.$end_date.'" GROUP BY cw_sap_wbs.wbs_id';
$wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$wbs_qry')");
$wbs_rslt = $wbs_info->result_array();
$wbs_info->next_result();
$pick_key = array_column($wbs_rslt ?? [],'wbs_id');
$pick_val = array_column($wbs_rslt ?? [],'wbs_desc');
$wbs_list = array_combine($pick_key ?? [], $pick_val ?? []);
if($wbs_rslt){
echo json_encode(array('success' => true,'message' => '', 'wbs_list' => $wbs_list));
}else{
echo json_encode(array('success' => false,'message' => 'No Record Found', 'wbs_list' => ""));
}
}
//FETCH EMPLOYEE CODE
public function get_employees_list(){
$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_month = $this->input->post('process_month');
$personal_code = $this->input->post('personal_code');
$project_id = $this->input->post('project_id');
$wbs_element = $this->input->post('wbs_element');
if($wbs_element){
$wbs_element = implode('","',array_filter($wbs_element ?? []) ?? []);
$wbs_qry = 'AND fms.wbs_element IN ("'.$wbs_element.'")';
}
//AND fms.sap_trans_status IN (2)
$employee_code_qry = 'SELECT CONCAT("<option value =",emp.employee_code," >",emp.emp_name," - ",emp.employee_code,"</option>") as result_data FROM cw_transactions_fms as fms INNER JOIN cw_employees as emp ON fms.employee_code = emp.employee_code WHERE fms.process_month = "'.$process_month.'" AND fms.personal_code = "'.$personal_code.'" AND fms.project_id = "'.$project_id.'" '.$wbs_qry.' AND fms.trans_status = 1 ';
$employee_code_info = $this->db->query("CALL sp_a_run ('SELECT','$employee_code_qry')");
$employee_code_rslt = $employee_code_info->result();
$employee_code_info->next_result();
$result_data = array_column($employee_code_rslt ?? [],'result_data');
$option = "<option value=''>---- Select Employee ----</option>".implode('',$result_data ?? []);
if($employee_code_rslt){
echo json_encode(array('success'=>TRUE,'option'=>$option));
}else{
echo json_encode(array('success'=>FALSE,'message'=>"No Employee Found for this process month"));
}
}
//COMPANY INFORMATION
public function company_info(){
$company = 'SELECT * 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->result();
$comp_info->next_result();
$this->company_info = $comp_result;
return $comp_result;
}
//PDF GENERATE
public function pdf_generation(){
$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);
}
$module_name = "employees";
$process_mode = (int)$this->input->post('process_mode');
$pdf_type = (int)$this->input->post('pdf_type');
$process_month = $this->input->post('process_month');
$start_date = date("Y-m-t",strtotime('01-'.$process_month));
$end_date = date("Y-m-d",strtotime($start_date));
$personal_code = $this->input->post('personal_code');
$project_id = $this->input->post('project_id');
$wbs_element = $this->input->post('wbs_element');
$process_by = (int)$this->input->post('process_by');
$emp_codes = $this->input->post('pdf_block_employees'); # EMPLOYEE WISE
if($wbs_element){
$wbs_in_qry = implode(',',array_filter($wbs_element ?? []) ?? []);
$wbs = explode(",",$wbs_in_qry ?? "");
$wbs_column = '"'.implode('","',$wbs ?? []).'"';
$wbs_qry = 'AND cw_transactions_fms.wbs_element IN ('.$wbs_column.')';
}
//GET COMMON DATA
//AND cw_transactions_fms.sap_trans_status IN (2)
$template_qry = 'SELECT cw_transactions_fms.process_month,cw_transactions_fms.personal_code,cw_transactions_fms.project_id,cw_transactions_fms.wbs_element,cw_sap_wbs.payslip_design FROM cw_transactions_fms INNER JOIN cw_sap_wbs ON cw_sap_wbs.wbs_id = cw_transactions_fms.wbs_element WHERE cw_transactions_fms.process_month = "'.$process_month.'" AND cw_transactions_fms.personal_code = "'.$personal_code.'" AND cw_transactions_fms.project_id = "'.$project_id.'" '.$wbs_qry.' AND cw_sap_wbs.wbs_start_date <= "'.$start_date.'" AND cw_sap_wbs.wbs_end_date >= "'.$end_date.'" AND cw_transactions_fms.trans_status = 1 AND cw_sap_wbs.trans_status = 1 GROUP BY cw_transactions_fms.project_id,cw_transactions_fms.wbs_element';
$template_info = $this->db->query($template_qry);
$template_rslt = $template_info->result();
$template_info->next_result();
if($template_rslt){
//PRINT TABLE
$table_qry = 'SELECT line_prime_table,line_prime_col,line_join_type,line_join_table,line_join_col,line_sort,print_table_for_id FROM cw_print_table WHERE trans_status = 1';
$table_data = $this->db->query("CALL sp_a_run ('SELECT','$table_qry')");
$table_result = $table_data->result_array();
$table_data->next_result();
foreach($table_result as $arr){
$print_table_result[$arr['print_table_for_id']][] = $arr;
}
//PRINT WHERE TABLE
$where_qry = 'SELECT where_condition,where_for_id FROM cw_print_table_where WHERE trans_status = 1';
$where_data = $this->db->query("CALL sp_a_run ('SELECT','$where_qry')");
$where_result = $where_data->result_array();
$where_data->next_result();
foreach($where_result as $arr){
$where_result[$arr['where_for_id']] = $arr;
}
//FORM SETTING
$form_qry = 'SELECT prime_module_id,prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value FROM cw_form_setting WHERE trans_status = "1"';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result_arr = $form_data->result_array();
$form_data->next_result();
foreach($form_result_arr as $arr){
$form_result_array[$arr['prime_module_id']][$arr['label_name']][] = $arr;
}
$result_array = array();
foreach($template_rslt as $value){
$process_month = $value->process_month;
$personal_code = $value->personal_code;
$project_id = $value->project_id;
$wbs_element = $value->wbs_element;
$pdf_template = (int)$value->payslip_design;
$result_array[] = $this->pdf_generation_common($module_name,$process_mode,$pdf_type,$process_month,$personal_code,$project_id,$wbs_element,$process_by,$pdf_template,$emp_codes,$print_table_result,$where_result,$form_result_array);
}
//GENERATE
if($process_mode === 1){
//TABLE VIEW
$tble_line = '';
$dw_all = '';
$column_array = array();
$merge_arr = array();
foreach($result_array as $val){
foreach($val as $subkey => $subval){
$pdf_type = (int)$subval['pdf_type'];
$dw_all = $subval['dw_all'];
$design_name= $subval['design_name'];
$file_path = base_url().$subval['folder']."/".$subval['file_name'].".pdf";
$enc_path = base64_encode($file_path);
if($pdf_type === 1){
$tble_line .= "<tr><td>{$subval['wbs_element']}</td><td>{$subval['emp_code']}</td><td>{$subval['emp_name']}</td><td><a class='btn btn-primary btn-sm' onclick=\"pdf_viewer('$enc_path')\"><span class='fa fa-eye'> </span>view</a></td></tr>";
}else{
$wbs_desc = strtoupper(str_replace('_', ' ', $subval['wbs_desc']));
$tble_line .= "<tr class='gradeU'><td>$wbs_desc</td><td><a class='btn btn-primary btn-sm' onclick = pdf_viewer('$enc_path')><span class='fa fa-eye' > </span> view </a></td></tr>";
$column_array[] = $subval['file_name'];
$merge_arr = ['process_month' => $process_month,'wbs_element' => $wbs_qry,'project_id' => $project_id, 'design_name' => $design_name];
}
}
}
if(count($column_array ?? []) > 0){
$column_array_val = '"'.implode('.pdf","', $column_array ?? []).'.pdf"';
$column_array_val = str_replace('/', '_',$column_array_val);
$import_url = site_url("pdf_generation/merge_all/");
$merge_arr_json = urlencode(json_encode($merge_arr));
$import_url = " $import_url?data=$merge_arr_json";
$tfoot_list = "<tfoot><tr class='gradeU'><td><a class='btn btn-primary btn-sm' onclick = download_zip('$dw_all','$column_array_val') ><span class='fa fa-download' > </span> Download All </a></td><td><a class='btn btn-primary btn-sm' href='$import_url' ><span class='fa fa-download' > </span> Merge All </a></td></tr></tfoot>";
}
if($pdf_type === 1){
$table_data = "<table class='table table-striped table-bordered' id='emp_details'><thead><tr><th>WBS Element</th><th>Employee Code</th><th>Employee Name</th><th>Action</th></tr></thead><tbody>$tble_line</tbody></table>";
}else{
$table_data = "<table class='table table-striped table-bordered' id='emp_details'><thead><tr><th>WBS Element</th><th>Action</th></tr></thead><tbody>$tble_line</tbody>$tfoot_list</table>";
}
echo json_encode(array('success' => true,'message' => 'PDF Generated Successfully', 'table_data' => $table_data));
}else{
//VIEW
$dw_all = '';
$tble_line = '';
$column_array = array();
$merge_arr = array();
foreach($result_array as $result){
foreach($result as $key => $value){
$wbs_desc = $value['wbs_desc'];
$design_name = $value['design_name'];
$emp_code = $value['employee_code'];
$dw_all = $value['dw_all'];
$db_name = $this->config->item("db_name");
$folder = 'pdf_generation/'.$module_name."/$design_name/$process_month/"."wbs_element_".$wbs_desc;
if($pdf_type === 1){
$file_name = $db_name."_".$emp_code;
$enc_file = base64_encode($file_name);
$enc_file_name = $enc_file."_".$emp_code;
$enc_file_name = $this->encryptFilename($enc_file_name,$db_name); //ENCRYPTION
$enc_file_name = $enc_file_name."_".$emp_code;
$directory = str_replace('application/controllers', '', __DIR__);
$pdf_file_name = $directory.$folder."/".$enc_file_name.".pdf";
if(file_exists($pdf_file_name)){
$file_path = base_url().$folder."/".$enc_file_name.".pdf";
$enc_path = base64_encode($file_path);
$tble_line .= "<tr><td>{$value['wbs_code']}</td><td>$emp_code</td><td>{$value['emp_name']}</td><td><a class='btn btn-primary btn-sm' onclick=\"pdf_viewer('$enc_path')\"><span class='fa fa-eye'> </span>view</a></td></tr>";
}
}else{
$cate_name = str_replace('/', '_',strtolower(str_replace(' ', '_', $value['wbs_code'])));
$file_name = $db_name."_".$cate_name;
$enc_file = base64_encode($file_name);
$enc_file_name = $this->encryptFilename($enc_file, $db_name); //ENCRYPTION
$enc_file_name = $enc_file_name."_".$wbs_desc;
$directory = str_replace('application/controllers', '', __DIR__);
$pdf_file_name = $directory.$folder."/".$enc_file_name.".pdf";
if(file_exists($pdf_file_name)){
$file_path = base_url().$folder."/".$enc_file_name.".pdf";
$enc_path = base64_encode($file_path);
$wbs_desc = strtoupper(str_replace('_', ' ', $wbs_desc));
$tble_line .= "<tr class='gradeU'><td>$wbs_desc</td><td><a class='btn btn-primary btn-sm' onclick = pdf_viewer('$enc_path')><span class='fa fa-eye' > </span> view </a></td></tr>";
$column_array[] = $enc_file_name;
$merge_arr = ['process_month' => $process_month,'wbs_element' => $wbs_qry,'project_id' => $project_id, 'design_name' => $design_name];
}
}
}
}
if(count($column_array ?? []) > 1){
$column_array_val = '"'.implode('.pdf","', $column_array ?? []).'.pdf"';
$column_array_val = str_replace('/', '_',$column_array_val);
$import_url = site_url("pdf_generation/merge_all/");
$merge_arr_json = urlencode(json_encode($merge_arr));
$import_url = " $import_url?data=$merge_arr_json";
$tfoot_list = "<tfoot><tr class='gradeU'><td><a class='btn btn-primary btn-sm' onclick = download_zip('$dw_all','$column_array_val') ><span class='fa fa-download' > </span> Download All </a></td><td><a class='btn btn-primary btn-sm' href='$import_url' >Merge All </a></td></tr></tfoot>";
}
if($pdf_type === 1){
$table_data = "<table class='table table-striped table-bordered' id='emp_details'><thead><tr><th>WBS Element</th><th>Employee Code</th><th>Employee Name</th><th>Action</th></tr></thead><tbody>$tble_line</tbody></table>";
}else{
$table_data = "<table class='table table-striped table-bordered' id='emp_details'><thead><tr><th>WBS Element</th><th>Action</th></tr></thead><tbody>$tble_line</tbody>$tfoot_list</table>";
}
if($tble_line){
echo json_encode(array('success' => true,'message' => 'Generated Files', 'table_data' => $table_data));
}else{
echo json_encode(array('success' => false,'message' => 'No Data Found', 'table_data' => ''));
}
}
}else{
echo json_encode(array('success' => false,'message' => 'No Data Found', 'table_data' => ''));
}
}
//GENERATIONS STARTS
public function pdf_generation_common($module_name,$process_mode,$pdf_type,$process_month,$personal_code,$project_id,$wbs_element,$process_by,$pdf_template,$emp_codes,$print_table_result,$where_result,$form_result_array){
$design_qry = 'SELECT print_design FROM cw_print_design WHERE print_design_for = "'.$pdf_template.'" AND trans_status = 1';
$design_data = $this->db->query("CALL sp_a_run ('SELECT','$design_qry')");
$design_result = $design_data->result();
$design_data->next_result();
$print_design_value = $design_result[0]->print_design;
$print_design_value = str_replace('~','"',$print_design_value);
//PRINT BLOCK
$block_qry = 'SELECT prime_print_info_id,prime_print_block_id,print_block_name,print_block_type,print_block_table,print_block_column,folder_column,set_password,pdf_paper_size,pdf_sheet_type,password_column FROM cw_print_info INNER JOIN cw_print_block ON cw_print_block.print_block_for = cw_print_info.prime_print_info_id WHERE cw_print_info.trans_status = 1 AND cw_print_block.trans_status = 1 AND prime_print_info_id = "'.$pdf_template.'" and print_info_module_id = "'.$module_name.'" AND print_block_module_id = "'.$module_name.'"';
$block_info = $this->db->query("CALL sp_a_run ('SELECT','$block_qry')");
$block_result = $block_info->result_array();
$block_info->next_result();
$print_id = $block_result[0]['prime_print_info_id'];
$design_name = strtolower(str_replace(' ', '_', $block_result[0]['print_block_name']));
//PRINT DESIGN
$design_qry = 'SELECT print_design FROM cw_print_design WHERE print_design_for = "'.$print_id.'" AND trans_status = 1';
$design_data = $this->db->query("CALL sp_a_run ('SELECT','$design_qry')");
$design_result = $design_data->result();
$design_data->next_result();
$print_design_value = $design_result[0]->print_design;
$print_design_value = str_replace('~','"',$print_design_value);
//EMPLOYEES
$emp_code_arr = implode('","', $emp_codes ?? []);
$emp_qry = 'SELECT prime_employees_id,emp_name,employee_code FROM `cw_employees` WHERE trans_status = 1 AND employee_code in ("'.$emp_code_arr.'") ';
$emp_info = $this->db->query($emp_qry);
$employee_rslt = $emp_info->result_array();
$emp_info->next_result();
foreach($employee_rslt as $arr){
$emp_rslt[$arr['employee_code']] = $arr;
}
$emp_codes = array_values(array_column($emp_rslt ?? [], 'employee_code') ?? []);
$emp_codes_array = array_filter($emp_codes ?? []);
$in_empcode = implode(',', $emp_codes_array ?? []);
$in_empcode = str_replace(',', '","', $in_empcode);
$folder_tbl_name = str_replace('cw_', '', $module_name);
$folder_tbl_name = strtolower(str_replace(" ","_",$folder_tbl_name));
$where_column = $this->where_month_value($module_name,$pdf_template,$process_month);
$where_column_arr = explode(',', $where_column ?? "");
$where_column_count = count($where_column_arr ?? []);
if($pdf_type === 1 && $process_by === 1){
$emp_code_cond = "and cw_employees.employee_code in(\"$in_empcode\")";
}
$where_month_col = " $where_column $emp_code_cond";
$payslip_based_on = "wbs_element";
//Get Payslip based on column name - START
$payslip_column_arr = $form_result_array[$module_name][$payslip_based_on][0];
$pick_table = $payslip_column_arr['pick_table'];
$pick_list = $payslip_column_arr['pick_list'];
$name_qry = 'SELECT '.$pick_list.' FROM '.$pick_table.' WHERE trans_status = 1';
$name_data = $this->db->query($name_qry);
$name_result = $name_data->result_array();
$name_data->next_result();
$pick_list_arr = explode(",",$pick_list ?? "");
$pick_id = $pick_list_arr[0];
$pick_name = $pick_list_arr[1];
$payslip_arr = array();
foreach($name_result as $key => $value){
$payslip_arr[$value[$pick_id]] = $value[$pick_name];
}
$final_qry_array = $this->load_all_data($block_result,$print_table_result,$form_result_array,$where_result,$where_month_col,$process_month,$in_empcode,$payslip_based_on,$personal_code,$project_id,$wbs_element);
$final_qry = $final_qry_array['final_qry'];
$map_column = $final_qry_array['map_column'];
$block_result = $final_qry_array['block_result'];
$assign_date_formate_list = $final_qry_array['assign_date_formate_list'];
$final_qry_data = $this->db->query($final_qry);
$final_qry_result = $final_qry_data->result();
$final_qry_data->next_result();
$folder_arr = array();
//Get Payslip based on column name - END
if((int)$process_mode === 1){
$tble_line = "";
$print_design= "";
foreach($block_result as $block){
$print_block_type = (int)$block['print_block_type'];
$print_block_column = $block['print_block_column'].',folder_column.folder_column_name';
if($final_qry_result){
$data['print_sts'] = true;
$emp_detail_arr = array();
$emp_data_arr = array();
$combine_data_arr = array();
$final_data_rslt = array();
foreach($final_qry_result as $rslt){
$payslip_rules = $rslt->payslip_rules;
$payslip_form = $rslt->payslip_form_no;
$count++;
$map_column = explode(",",$print_block_column ?? "");
$td_line = "";
$print_design = $print_design_value;
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$column = $map_column[1];
$value = $rslt->$column;
$replace_val = "@".$column."@";
if($column == 'net_pay'){
$value = $rslt->$column;
$value = $value;
$print_design = str_replace($replace_val,$value,$print_design);
$net_pay_val = $value;
$net_pay_words = $this->numbertowords($net_pay_val);
$net_pay_words = strtoupper($net_pay_words);
$print_design = str_replace("@net_pay_words@",$net_pay_words,$print_design);
}
$today_date = date('d-m-Y');
$print_design = str_replace("@today_date@",$today_date,$print_design);
//FOR PAYSLIP FORM AND RULES STATIC CODE
$print_design = str_replace("@payslip_rules@",$payslip_rules,$print_design);
$print_design = str_replace("@payslip_form@",$payslip_form,$print_design);
if($print_block_type === 1){
$print_design = str_replace($replace_val,$value,$print_design);
foreach($assign_date_formate_list as $key=>$formate){
if($column == 'transactions_month'){//transactions month static updated
$start = "@".$key."_";
$end = "_".$key."@";
$replace_val = $start.$column.$end;
$value = date('Y-m-d',strtotime("01-".$rslt->$column));
$date_value = date_create($value);
$replace_value = strtoupper(date_format($date_value,$formate));
$print_design = str_replace($replace_val,$replace_value,$print_design);
}else{//not static month updated
$start = "@".$key."_";
$end = "_".$key."@";
$replace_val = $start.$column.$end;
$replace_val = $start.$column.$end;
$value = date('Y-m-d',strtotime("01-".$rslt->$column));
$date_value = date_create($value);
$replace_value = date_format($date_value,$formate);
$print_design = str_replace($replace_val,$replace_value,$print_design);
}
}
}else
if($print_block_type === 2){
$td_line .= "<td style='text-align:center;'>$value</td>";
}
if($count === 1){
$head_name = ucwords(str_replace("_"," ",$column));
if(($value === "") || ($value === "0.00") || ($value === "0") || (!$value)){
$th_line = "";
}else{
$th_line .= "<th style='text-align:center;'>$head_name</th>";
}
}
$emp_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
if($column === 'folder_column_name'){
$payslip_based_id = strtolower(str_replace(" ", "_", $rslt->folder_column_name));
$payslip_wbs_desc = strtolower(str_replace(" ", "_", $payslip_arr[$value]));
}
}
if($print_block_type === 2){
if($count === 1){
if(($value === "") || ($value === "0.00") || ($value === "0") || (!$value)){
$th_line = "";
}else{
$th_line = "$th_line";
$tr_line .= "<tr>$td_line</tr>";
}
}
}
$print_design = str_replace("<br>","",$print_design);
if($print_block_type === 2){
$table_list = "<table style='width:100%;'><thead>$th_line</thead><tbody>$tr_line</tbody></table>";
$replce_block = "@".strtolower(str_replace(" ","_",$print_block_name))."@";
$print_design = str_replace($replce_block,$table_list,$print_design);
}
$emp_data_arr[$payslip_based_id][$emp_code]['emp_code'] = $emp_code;
$emp_data_arr[$payslip_based_id][$emp_code]['emp_name'] = $emp_name;
$emp_data_arr[$payslip_based_id][$emp_code]['payslip_based_id'] = $payslip_based_id;
$emp_data_arr[$payslip_based_id][$emp_code]['payslip_wbs_desc'] = $payslip_wbs_desc;
$emp_data_arr[$payslip_based_id][$emp_code]['print_design'] = $print_design;
}
}
}
if((int)$pdf_type === 1){
$folder_arr = $this->single_pdf($emp_data_arr,$design_name,$payslip_based_on,$folder_tbl_name,$process_month,$pdf_type);
}else
if((int)$pdf_type === 2){
$folder_arr = $this->combine_pdf($emp_data_arr,$design_name,$payslip_based_on,$folder_tbl_name,$process_month,$pdf_type);
}
}else
if((int)$process_mode === 2){
$folder_arr = array();
$dw_all = $folder_tbl_name."/".$design_name."/".$process_month;
foreach($final_qry_result as $key => $value){
$based_on = strtolower($payslip_arr[$value->folder_column_name]);
$wbs_desc = preg_replace('/[^A-Za-z]/', '_', $based_on);
if((int)$pdf_type === 1){
$folder_arr[] = ['employee_code' => $value->employee_code,'emp_name' => $value->emp_name,'wbs_code' => $value->folder_column_name,'design_name' => $design_name,'wbs_desc' => $wbs_desc,'dw_all' => $dw_all];
}else{
$folder_arr[$based_on] = ['employee_code' => $value->employee_code,'emp_name' => $value->emp_name,'wbs_code' => $value->folder_column_name,'design_name' => $design_name,'wbs_desc' => $wbs_desc,'dw_all' => $dw_all];
}
}
}
return $folder_arr;
}
//SINGLE PDF GENERATION
public function single_pdf($emp_data_arr,$design_name,$payslip_based_on,$folder_tbl_name,$process_month,$pdf_type){
$get_pdf_design = "";
$folder_arr = array();
foreach($emp_data_arr as $based_on => $emp_data){
foreach($emp_data as $key => $emp_detail){
$db_name = $this->config->item("db_name");
$emp_code = $emp_detail['emp_code'];
$emp_name = $emp_detail['emp_name'];
$file_name = $db_name."_".$emp_code;
$enc_file = base64_encode($file_name);
$enc_file_name = $enc_file."_".$emp_code;
$enc_file_name = $this->encryptFilename($enc_file_name,$db_name); //ENCRYPTION
$enc_file_name = $enc_file_name."_".$emp_code;
$cate_name = str_replace('/', '_',strtolower(str_replace(' ', '_', $emp_detail['payslip_based_id'])));
$wbs_desc = str_replace('/', '_',strtolower(str_replace(' ', '_', $emp_detail['payslip_wbs_desc'])));
$wbs_desc = preg_replace('/[^A-Za-z]/', '_', $wbs_desc);
$print_design = $emp_detail['print_design'];
$print_design = str_replace('~', '"', $print_design);
$print_design = "<!DOCTYPE html><head></head><style>table{border-collapse: collapse;}table td.fr-highlighted,.fr-view table th.fr-highlighted{border:1px solid black !important;vertical-align: text-top;}</style><body>".$print_design."</body></html>";
$design_name = str_replace(' ', '_', $design_name);
$cate_name = str_replace(' ', '_', $cate_name);
$folder_tbl_name= str_replace(' ', '_', $folder_tbl_name);
$folder = 'pdf_generation/'.$folder_tbl_name."/$design_name/$process_month/".$payslip_based_on."_".$wbs_desc;
$folder = strtolower($folder);
$dom = new \DOMDocument();//SUPPRESS DATA
$dom->loadHTML($print_design);
$xpath = new \DOMXPath($dom);
//loop all <tr> element.
foreach($xpath->query('//tr') as $tr){
$tds = $tr->getElementsByTagName('td');
for ($i = 0; $i <= $tr->length; $i++){
// get table cell value.
$table_cell_value = $tds->item($i)->nodeValue;
if($table_cell_value === '0.00'){
if($tr->parentNode){
$tr->parentNode->removeChild($tr);
}
}
}
}
//GET THE RESULTS
$final_rslt = preg_replace('~<(?:!DOCTYPE|/?(?:html|body))[^>]*>\s*~i', '', $dom->saveHTML());
//PATH CREATION TO STORE THE FILES
$oldmask = umask(0);
if(!file_exists($folder)){
mkdir($folder, 0777, true); # IF NOT CREATE
}
//FILE WRITE AS HTML
file_put_contents($folder."/".$enc_file_name.".html" , $final_rslt);
chmod($folder."/".$enc_file_name.".html", 0777);
umask($oldmask);
$dw_all = $folder_tbl_name."/".$design_name."/".$process_month;
$directory = str_replace('application/controllers', '', __DIR__);
$html_file_name = $directory.$folder."/".$enc_file_name.".html";
$pdf_file_name = $directory.$folder."/".$enc_file_name.".pdf";
if(file_exists($html_file_name)){
//CONVERT HTML FILE TO PDF
$command = "xvfb-run wkhtmltopdf --orientation portrait $html_file_name $pdf_file_name";
$output = shell_exec($command);
$folder_arr[] = array('folder' => $folder,'file_name' => $enc_file_name,'emp_code' => $emp_code,'emp_name' => $emp_name,'wbs_element' => $cate_name,'pdf_type' => $pdf_type,'wbs_desc' => $wbs_desc);
unlink($html_file_name);
}
}
}
return $folder_arr;
}
//COMBINED PDF GENERATION
public function combine_pdf($combine_data_arr,$design_name,$payslip_based_on,$folder_tbl_name,$process_month,$pdf_type){
$folder_arr = array();
foreach($combine_data_arr as $based_on => $emp_data_arr){
$get_pdf_design = "";
foreach($emp_data_arr as $i => $emp_detail){
$emp_code = $emp_detail['emp_code'];
$cate_name = str_replace('/', '_',strtolower(str_replace(' ', '_', $emp_detail['payslip_based_id'])));
$wbs_desc = str_replace('/', '_', strtolower(str_replace(' ', '_', $emp_detail['payslip_wbs_desc'])));
$wbs_desc = preg_replace('/[^A-Za-z]/', '_', $wbs_desc);
$print_design = $emp_detail['print_design'];
$print_design = str_replace('~', '"', $print_design);
//SUPPRESS DATA
$dom = new \DOMDocument();
$dom->loadHTML($print_design);
$xpath = new \DOMXPath($dom);
//loop all <tr> element.
foreach($xpath->query('//tr') as $tr){
$tds = $tr->getElementsByTagName('td');
for($i = 0; $i <= count($tr ?? []); $i++) {
// get table cell value.
$table_cell_value = $tds->item($i)->nodeValue;
if($table_cell_value === '0.00'){
if($tr->parentNode){
$tr->parentNode->removeChild($tr);
}
}
}
}
// get the result
$print_design = preg_replace('~<(?:!DOCTYPE|/?(?:html|body))[^>]*>\s*~i', '', $dom->saveHTML());
$get_pdf_design .= "<div style='page-break-inside: avoid;'>$print_design </div>";
$final_rslt = "<!DOCTYPE html><head><style> table {border-collapse: collapse;}table td.fr-highlighted,.fr-view table th.fr-highlighted{border:1px solid black !important;vertical-align: text-top;}</style></head><body>".$get_pdf_design."</body></html>";
}
$db_name = $this->config->item("db_name");
$design_name = str_replace(' ', '_', $design_name);
$cate_name = str_replace(' ', '_', $cate_name);
$cate_name = str_replace('/', '_',$cate_name);
$folder_tbl_name = str_replace(' ', '_', $folder_tbl_name);
$folder = 'pdf_generation/'.$folder_tbl_name."/$design_name/$process_month/".$payslip_based_on."_".$wbs_desc;
$file_name = $db_name . "_" . $cate_name;
$enc_file = base64_encode($file_name);
$enc_file_name = $this->encryptFilename($enc_file, $db_name); //ENCRYPTION
$file_name = $enc_file_name."_".$wbs_desc;
//PATH CREATION TO STORE THE FILES
$oldmask = umask(0);
if(!file_exists($folder)){
mkdir($folder, 0777, true); # IF NOT CREATE
}
//FILE WRITE AS HTML
file_put_contents($folder."/".$file_name.".html" , $final_rslt);
chmod($folder."/".$file_name.".html", 0777);
umask($oldmask);
$dw_all = $folder_tbl_name."/".$design_name."/".$process_month;
$directory = str_replace('application/controllers', '', __DIR__);
$html_file_name = $directory.$folder."/".$file_name.".html";
$pdf_file_name = $directory.$folder."/".$file_name.".pdf";
if(file_exists($html_file_name)){
//CONVERT HTML FILE TO PDF
$command = "xvfb-run wkhtmltopdf --orientation portrait $html_file_name $pdf_file_name";
$output = shell_exec($command);
$folder_arr[$based_on] = array('folder' => $folder,'file_name' => $file_name,'wbs_element' => $cate_name,'pdf_type' => $pdf_type,'wbs_desc' => $wbs_desc,'dw_all' => $dw_all, 'design_name'=> $design_name);
unlink($html_file_name);
}
}
return $folder_arr;
}
//MERGE ALL THE FILES USING PDF MERGER
public function merge_all(){
$pdf = new PDFMerger;
$files_array = urldecode($this->input->get('data'));
$filter_data = json_decode($files_array, true);
$process_month = $filter_data['process_month'];
$wbs_qry = $filter_data['wbs_element'];
$project_id = $filter_data['project_id'];
$design_name = $filter_data['design_name'];
$db_name = $this->config->item("db_name");
//AND sap_trans_status = 2
$trans_id = 'SELECT cw_sap_project.pro_desc,process_month,cw_transactions_fms.project_id,cw_transactions_fms.wbs_element,cw_sap_wbs.wbs_desc from cw_transactions_fms INNER JOIN cw_sap_wbs ON cw_transactions_fms.wbs_element = cw_sap_wbs.wbs_id INNER JOIN cw_sap_project ON cw_sap_project.pro_id = cw_transactions_fms.project_id WHERE cw_transactions_fms.trans_status = 1 AND cw_sap_wbs.trans_status = 1 AND project_id = "'.$project_id.'" AND process_month = "'.$process_month.'" '.$wbs_qry.' GROUP BY wbs_element ';
$trans_info = $this->db->query("CALL sp_a_run ('SELECT','$trans_id')");
$trans_rslt = $trans_info->result();
$trans_info->next_result();
//BUILD FILE PATH
$pro_desc = '';
foreach($trans_rslt as $folder_path){
$pro_desc = strtoupper($folder_path->pro_desc);
$file_name = strtolower(str_replace(" ", "_",$folder_path->wbs_element));
$file_desc = strtolower(str_replace(" ", "_",$folder_path->wbs_desc));
$file_desc = preg_replace('/[^A-Za-z]/', '_', $file_desc);
$folder_name = strtolower("wbs_element_".$file_desc);
$file_name = $db_name."_".$file_name;
$enc_file = base64_encode($file_name);
$enc_file_name = $this->encryptFilename($enc_file,$db_name);
$payslip_based = $enc_file_name."_".$file_desc;
$file_path = "pdf_generation/employees/$design_name/$process_month/$folder_name/$payslip_based.pdf";
if(file_exists($file_path)){
$pdf->addPDF($file_path, 'all');
}
}
//MERGE ALL
$pdf->merge('download', $pro_desc.".pdf");
}
//FETCH DATA BASED ON THE FILTERS
public function load_all_data($block_result,$print_table_result,$form_result_array,$where_result,$where_month_col,$process_month,$in_empcode,$payslip_based_on,$personal_code,$project_id,$wbs_element){
foreach($block_result as $block){
$prime_print_block_id = $block['prime_print_block_id'];
$print_block_name = $block['print_block_name'];
$print_block_type = (int)$block['print_block_type'];
$print_block_table = $block['print_block_table'];
$print_block_column = $block['print_block_column'];
$folder_column = $block['folder_column'];
$table_rslt = $print_table_result[$prime_print_block_id];
$line_table_query = "";
foreach($table_rslt as $table_result){
$line_prime_table = $table_result['line_prime_table'];
$line_prime_col = $table_result['line_prime_col'];
$line_join_type = $table_result['line_join_type'];
$line_join_table = $table_result['line_join_table'];
$line_join_col = $table_result['line_join_col'];
$line_sort = $table_result['line_sort'];
$module_name = str_replace("cw_","",$line_prime_table);
$prime_id = "prime_".$module_name."_id";
$join_module_name = str_replace("cw_","",$line_join_table);
$join_prime_id = "prime_".$join_module_name."_id";
if((int)$line_sort === 1){
$line_prime_table = " $line_prime_table ";
$line_join_table = " $line_join_table on $line_join_col = $line_prime_col ";
$line_table_query .= " $line_prime_table $line_join_type join $line_join_table";
}else{
$line_table_query .= " $line_join_type join $line_join_table on $line_join_col = $line_prime_col ";
}
}
if(!$line_table_query){
$module_name = str_replace("cw_","",$print_block_table);
$prime_id = "prime_".$module_name."_id";
$line_table_query = " $print_block_table ";
}
if(!$print_block_column){
$print_block_column = "*";
}else{
$select_query = "";
$select_query.= "$folder_column as folder_column_name,";
$pick_query = "";
$map_column = explode(",",$print_block_column ?? "");
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$table_name = $map_column[0];
$column = $map_column[1];
$control_name = str_replace('cw_',"",$table_name);
if($control_name === "transactions" || $control_name === "transactions_fms"){
$control_name = "employees";
}
$form_result = $form_result_array[$control_name][$column];
foreach($form_result as $form){
$prime_form_id = (int)$form['prime_form_id'];
$view_name = $form['view_name'];
$label_name = $form['label_name'];
$field_type = (int)$form['field_type'];
$pick_list_type = (int)$form['pick_list_type'];
$pick_list = $form['pick_list'];
$pick_table = $form['pick_table'];
$auto_prime_id = $form['auto_prime_id'];
$auto_dispaly_value = $form['auto_dispaly_value'];
if($label_name){
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else
if(($field_type === 2) || ($field_type === 3)){
$label_ytd = $label_name."_ytd";
$select_ytd_query .= "sum($table_name.$label_name) as $label_ytd, ";
$select_query .= "$table_name.$label_name , ";
}else{
$select_query .= "$table_name.$label_name , ";
}
}
}
}
}
$where_trans = "";
$where_trans_info = explode(",",$print_block_table ?? "");
foreach($where_trans_info as $trans_info){
if($trans_info === "cw_transactions"){
$select_query .= "cw_transactions.transactions_month ,";
}
if($trans_info === "cw_transactions_fms"){
$select_query .= "cw_transactions_fms.transactions_month , ";
}
$where_trans .= " $trans_info.trans_status = 1 and ";
}
$select_query .= "sap_personal_code.payslip_rules,sap_personal_code.payslip_form_no , ";
$pick_query .= "inner join cw_sap_personal_area as sap_personal_code on sap_personal_code.personal_code = cw_employees.personal_code ";
$where_trans = rtrim($where_trans,'and ');
$where_rslt = $where_result[$prime_print_block_id];
if(!empty($where_rslt)){
$where_condition = str_replace('^','"',$where_rslt['where_condition']);
$where_condition = str_replace('@logged_id@',$emp_id,$where_condition);
$where_condition = str_replace('@input_month@',$payslip_month,$where_condition);
$session_date_list = array("logged_DMY"=>"d-m-Y","logged_YMD"=>"Y-m-d","logged_MY"=>"m-Y","logged_YM"=>"Y-m","logged_Y"=>"Y");
$session_query = 'SELECT session_value FROM cw_session_value WHERE session_for = 1 AND trans_status = "1"';
$session_data = $this->db->query("CALL sp_a_run ('SELECT','$session_query')");
$session_result = $session_data->result();
$session_data->next_result();
foreach($session_result as $rslt){
$session_value = $rslt->session_value;
if($session_value !== "access_data"){
$exist_val = "@".$session_value."@";
if($session_date_list[$session_value]){
$date_formate = $session_date_list[$session_value];
$saved_session_val = date($date_formate);
}else{
$saved_session_val = $this->session->userdata($session_value);
}
$where_condition = str_replace($exist_val,$saved_session_val,$where_condition);
}
}
}
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
$where_month_col.= 'AND cw_transactions_fms.personal_code = "'.$personal_code.'" AND cw_transactions_fms.project_id = "'.$project_id.'" AND cw_transactions_fms.wbs_element = "'.$wbs_element.'"';
$order_qry = 'ORDER BY cw_transactions_fms.emp_name ';
$final_qry = "SELECT $select_query FROM $line_table_query $pick_query WHERE $where_trans $where_condition $where_month_col $order_qry";
$tr_line = "";
$th_line = "";
$count = 0;
$assign_date_formate_list = array("DMY"=>"d-m-Y","YMD"=>"Y-m-d","MY"=>"F-Y","YM"=>"Y-F","D"=>"d","M"=>"M","Y"=>"Y");
}
$map_column = explode(",",$print_block_column ?? "");
$final_qry_array = array('final_qry'=>$final_qry,'map_column'=>$map_column,'print_design'=>$print_design,'assign_date_formate_list'=>$assign_date_formate_list,'block_result'=>$block_result);
return $final_qry_array;
}
//WHERE CONDITION
public function where_month_value($module_name,$pdf_template,$process_month){
$month_col_qry = 'SELECT cw_print_block.month_column,cw_print_block.print_block_table FROM cw_print_info INNER JOIN cw_print_block ON cw_print_block.print_block_for = cw_print_info.prime_print_info_id WHERE print_info_module_id = "'.$module_name.'" and cw_print_info.trans_status = 1 and prime_print_info_id = "'.$pdf_template.'" and print_block_type = 1';
$month_col_info = $this->db->query("CALL sp_a_run ('SELECT','$month_col_qry')");
$month_col_rslt = $month_col_info->result();
$month_col_info->next_result();
$where_month = $month_col_rslt[0]->month_column;
$where_table = $month_col_rslt[0]->print_block_table;
$module_name = implode('","',explode(',', $where_table ?? "") ?? []);
$module_name = str_replace('cw_', '', $module_name);
$form_set_qry = 'SELECT field_type,label_name,date_type FROM cw_form_setting WHERE prime_module_id IN ("'.$module_name.'") AND cw_form_setting.trans_status = 1';
$form_set_info = $this->db->query("CALL sp_a_run ('SELECT','$form_set_qry')");
$form_set_rslt = $form_set_info->result_array();
$form_set_info->next_result();
$form_set_rslt = array_reduce($form_set_rslt ?? [], function($result, $arr){
$result[$arr['label_name']] = $arr;
return $result;
}, array());
$where_month = explode(',', $where_month ?? "");
$where_column = "";
foreach($where_month as $key => $where_col){
$get_label_arr= explode('.', $where_col ?? "");
$table_name = $get_label_arr[0];
$label_name = $get_label_arr[1];
$field_type = $form_set_rslt[$label_name]['field_type'];
$date_type = $form_set_rslt[$label_name]['date_type'];
if($label_name ==="transactions_month" && $table_name ==="cw_transactions"){
$field_type = 1;
}
if((int)$field_type === 4 && (int)$date_type === 1){
$where_column .= "and DATE_FORMAT(STR_TO_DATE($table_name.$label_name,\"%Y-%m-%d\"),\"%m-%Y\") = \"$process_month\" ";
}else{
$where_column .= " and $table_name.$label_name =\"$process_month\" ";
}
}
return $where_column;
}
//DOWNLOAD ALL
public function download_zip(){
$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 = "./pdf_generation/".$path_name."/";
$zip = new ZipArchive();
$filename = "./pdf_generation/".$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.$file.'/';
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));
}
}