File: /home/cafsindia/uds.cafsinfotech.in/application/controllers_bk/Pre_audit_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Pre_audit_report extends Action_controller{
public function __construct(){
parent::__construct('pre_audit_report');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//PAGE INFO FUNCTION
$this->page_info();
$data['encKey'] = $this->generateKey();
$data['module_sts'] = (int)$this->module_sts;
$data['quick_link'] = $this->quick_link;
$data['pick_list'] = $this->pick_list;
$data['form_info'] = $this->form_info;
$data['table_head'] = $this->table_head;
$data['fliter_list'] = $this->fliter_list;
$data['freeze_list'] = $this->freeze_list;
//PERSONAL AREA
$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 = "";
foreach($per_area_rslt as $for){
$personal_code = $for['personal_code'];
$personal_name = $for['personal_name'];
if($personal_code !== ""){
$per_area_list .= "<option data-value='".$personal_code."' value='".trim($personal_code)."' >".trim($personal_name)."</option>";
}
}
$data['per_area_list'] = $per_area_list;
$this->load->view("$this->control_name/manage",$data);
}
//BSK START FOR PROJECT ID FETCH FROM MI FMS BASED ON PERSONAL CODE
public function project_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');
$search_month = $this->input->post('search_month');
$area_access = $this->logged_area_access;
$pro_id_qry = 'SELECT pro_id,pro_desc FROM `cw_sap_project` inner join cw_monthly_input_fms on cw_monthly_input_fms.project_id = cw_sap_project.pro_id where cw_monthly_input_fms.process_month = "'.$search_month.'" and cw_monthly_input_fms.trans_status = 1 and entry_status = 1 and pro_personal_area_id = "'.$personal_code.'" GROUP BY cw_sap_project.pro_id';
$pro_id_info = $this->db->query("CALL sp_a_run ('SELECT','$pro_id_qry')");
$pro_id_rslt = $pro_id_info->result_array();
$pro_id_info->next_result();
echo json_encode($pro_id_rslt);
}
//GET WBS FROM MONTHLY INPUT FMS BSK START
public function fil_wbs_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 = $this->input->post('project');
$search_month = $this->input->post('search_month');
$fil_wbs_qry = 'select cw_sap_wbs.prime_sap_wbs_id,cw_sap_wbs.wbs_id,cw_sap_wbs.wbs_desc from cw_sap_wbs inner join cw_monthly_input_fms on cw_monthly_input_fms.wbs_element = cw_sap_wbs.wbs_id where cw_monthly_input_fms.process_month = "'.$search_month.'" and wbs_project_id = "'.$project.'" and cw_monthly_input_fms.trans_status = 1 GROUP BY cw_sap_wbs.prime_sap_wbs_id';
$fil_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$fil_wbs_qry')");
$fil_wbs_rslt = $fil_wbs_info->result_array();
$fil_wbs_info->next_result();
echo json_encode($fil_wbs_rslt);
}
public function pre_audit_report(){ #cw_pay_structure.gross
$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);
}
$this->search_info();
#Retreiving cw_employees table column names from action controller.
$search_query = str_replace("@SELECT@",$this->select_query,$this->base_query);
$search_query = str_replace(",",",month_fms.",$search_query);
$search_query = str_replace("cw_pre_audit_salary","pre",$search_query);
$search_query = str_replace("month_fms.position","cw_sap_position.position_name as `position`",$search_query);
$search_query = str_replace("month_fms.project_id","cw_sap_project.pro_desc as `project_id`",$search_query);
$search_query = str_replace("month_fms.role","cw_category.category_name as `category`",$search_query);
$search_query = str_replace("month_fms.activity_no","cw_sap_activity.act_no as `activity_no`",$search_query);
$search_query = str_replace("month_fms.date_of_birth,","",$search_query);
$search_query = str_replace("month_fms.date_of_joining,","",$search_query);
$search_query = str_replace("month_fms.msal_approve_date,","",$search_query);
$search_query = str_replace("month_fms.msal_reject_date,","",$search_query);
$search_query = str_replace("month_fms.role","cw_category.category_name as `category`",$search_query);
$search_query = str_replace("month_fms.termination_status",'CASE WHEN month_fms.termination_status = 0 THEN "Active" ELSE "Inactive" END as `termination_status`',$search_query);
#Static inputs.
$mi_emp_select_qry = str_replace(" from pre",',CASE WHEN month_fms.entry_status = 0 THEN "Pending" WHEN month_fms.entry_status = 1 THEN "Submitted to Checker" ELSE "Input Given" END as `Entry Status`, CASE WHEN month_fms.check_status = 0 THEN "Pending" WHEN month_fms.check_status = 1 THEN "Approved" ELSE "Rejected" END as `Check Status`, CASE WHEN month_fms.payroll = 1 THEN "Yes" ELSE "No" END as `payroll`, month_fms.remarks',$search_query);
$search_month = $this->input->post('search_month');
$project_id = $this->input->post('project_id');
$personal_code = $this->input->post('personal_code');
$type = $this->input->post('type');
$filter_wbs = implode(",",$this->input->post('fil_wbs_element') ?? []);
if($filter_wbs){
$fil_wbs_element = '"'.str_replace(',','","',$filter_wbs).'"';
$wbs_qry = ' and pre.wbs_element IN ('.$fil_wbs_element.')';
}
if($project_id ){
$project_qry = ' and pre.project_id = "'.$project_id.'" ';
}
if($type !== ""){
$audit_qry = ' and pre.audit_status = "'.$type.'" ';
}
$db_name = $this->config->item("db_name");
$pre_qry = 'SELECT COLUMN_NAME AS col_name, COLUMN_KEY AS key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = "'.$db_name.'" AND TABLE_NAME = "cw_pre_audit_salary" and COLUMN_NAME not in ("prime_pre_audit_salary_id","prime_monthly_input_fms_id","prime_trans_process_id","employee_code","emp_name","process_month","position","wbs_element","project_id","rev_activity","category","activity_id","trans_created_by","trans_created_date","trans_updated_by","trans_updated_date","trans_deleted_by","trans_deleted_date","trans_status","sal_status","deploy_status","mandays_status","allowance_status","sal_audit_status","deploy_audit_status","mandays_audit_status","allowance_audit_status","audit_status","chk_date")';
$pre_info = $this->db->query("CALL sp_a_run ('SELECT','$pre_qry')");
$pre_rslt = $pre_info->result_array();
$pre_info->next_result();
$pre_arr = array_column($pre_rslt ?? [],"col_name","col_name");
$pre_audit_qry = '';
foreach($pre_arr as $pre){
$pre_audit_qry .= "pre.$pre,";
}
$pre_qry = ''.$mi_emp_select_qry.','.$pre_audit_qry.'CASE WHEN audit_status = 0 THEN "Pending" WHEN audit_status = 1 THEN "Approved" ELSE "Rejected" END AS `Audit Status` from cw_pre_audit_salary as pre join cw_employees as emp join cw_monthly_input_fms as month_fms on emp.employee_code = pre.employee_code and month_fms.prime_monthly_input_fms_id = pre.prime_monthly_input_fms_id inner join cw_sap_position on cw_sap_position.position_code = month_fms.position INNER JOIN cw_category ON cw_category.prime_category_id = month_fms.role INNER JOIN cw_sap_project ON cw_sap_project.pro_id = month_fms.project_id INNER JOIN cw_sap_activity ON cw_sap_activity.prime_sap_activity_id = month_fms.activity_no where pre.process_month = "'.$search_month.'" and month_fms.personal_code = "'.$personal_code.'" '.$wbs_qry.' '.$project_qry.' '.$audit_qry .' ';
$pre_info = $this->db->query("CALL sp_a_run ('SELECT','$pre_qry')");
$pre_rslt = $pre_info->result_array();
$pre_info->next_result();
$lable_qry = 'select label_name,view_name from cw_form_setting WHERE prime_module_id in ("pre_audit_salary","employees") ';
$label_info = $this->db->query("CALL sp_a_run ('SELECT','$lable_qry')");
$label_rslt = $label_info->result_array();
$newArray = [];
$i = 0;
foreach($pre_rslt as $key[$i] => $val){
foreach($val as $subkey => $subval){
if($subkey !== "prime_pre_audit_salary_id" && $subkey !== "msal_approve_date" && $subkey !== "msal_reject_date" && $subkey !== "checker_date"){
$matchFound = false;
foreach($label_rslt as $item){
$k = $item['label_name'];
$s = $item['view_name'];
if($subkey === "checker_submitted_date" && $subval){
$subval = date('d/m/Y h:m:s', strtotime($subval));
}
if($subkey === "submitted_date" && $subval){
$subval = date('d/m/Y h:m:s', strtotime($subval));
}
if($subkey === $k){
$newArray[$i][$s] = $subval;
$matchFound = true;
break; // Exit the inner loop since a match was found
}
}
if(!$matchFound){
$newArray[$i][$subkey] = $subval;
}
}
}$i++;
}
$table = $this->generateTable($newArray);
$pre_info->next_result();
if($pre_rslt){
echo json_encode(array("success"=>TRUE,"message"=>"Records Found.!","data"=>$table));
}else{
echo json_encode(array("success"=>FALSE,"message"=>"No Records Found.!"));
}
}
//GENERATING TABLE FOR LOAN BALANCE REPORT !
public function generateTable($data){
$table = '<table class="table table-striped table-bordered" id="role">';
$headerrow = '<thead><tr>';
foreach($data[0] as $key => $value){
$headerrow .= '<th scope="col">'.$key.'</th>';
}
$headerrow .= '</tr></thead><tbody>';
$table .= $headerrow;
foreach($data as $item){
$row = '<tr>';
foreach ($item as $value){
$row .= '<td>' .$value. '</td>';
}
$row .= '</tr>';
$table .= $row;
}
$table .= '</tbody></table>';
return $table;
}
}
?>