File: /home/cafsindia/uds.cafsinfotech.in/application/controllers_bk/Pay_structure_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Pay_structure_report extends Action_controller{
public $pay_add_filter_arr = array();
// public $pay_import_arr = array();
public function __construct(){
parent::__construct('pay_structure_report');
// $this->collect_base_info();
//for filter purpose
$this->pay_add_filter_pick_column();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//FOR GET PAY STRUCTURE DATA BASED ON PAY STRUCTURE FILTER SETTINGS
$data['pay_add_filter_setting'] = $this->pay_add_filter_col_setting();
//FOR GET PAY STRUCTURE DATA ARRAY DETAILS BASED ON PAY STRUCTURE FILTER SETTINGS
$data['pay_add_filter_pick_column'] = $this->pay_add_filter_arr;
$this->page_info();
$role_qry = 'select prime_category_id,category_name from cw_category where trans_status = 1 and prime_category_id not in(1)';
$role_info = $this->db->query("CALL sp_a_run ('SELECT','$role_qry')");
$role_rslt = $role_info->result_array();
$role_info->next_result();
$pick_key = array_column($role_rslt ?? [], 'prime_category_id');
$pick_val = array_column($role_rslt ?? [], 'category_name');
$final_pick = array_combine( $pick_key ?? [], $pick_val ?? []);
$data['role_list'] = array("" => "---- Select ----") + $final_pick;
$data['get_depend_prime_id'] = $this->get_depend_fun();
$data['depend_label_id'] = $this->depend_label_fun();
//select query for get personal area details
$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.'") ');
//Generate list for Datalist
$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;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//PAYROLL EXIST CHECK FUNCTION
public function payroll_exist_arr(){
$payroll_exist_arr = array();
//FUNCTION FOR GET PAY STRUCTURE FILTER DETAILS BASED ON PAY STRUCTURE FILTER SETTINGS
$pay_filter_setting = $this->pay_add_filter_col_setting();
$table_join_qry = "";
foreach($pay_filter_setting as $setting){
$label_name = $setting->label_name;
$pick_list = $setting->pick_list;
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($label_name === "role"){
$label_name = "category";
}
$mandatory_col = (int)$setting->mandatory_column;
if($mandatory_col === 1){
$table_join_qry .= ' cw_pay_structure.'.$label_name.' = cw_transactions_fms.'.$label_name.' and';
}
}
$table_join_qry = rtrim($table_join_qry,"and");
$payroll_ext_check_qry = 'select prime_pay_structure_id as prime_id from cw_transactions_fms inner join cw_pay_structure on (cw_pay_structure.category = cw_transactions_fms.role and '.$table_join_qry.') where date_format(str_to_date(cw_transactions_fms.process_month, "%m-%Y") , "%Y-%m") >= date_format(cw_pay_structure.from_date, "%Y-%m") and cw_transactions_fms.trans_status = 1';
$payroll_ext_check_info = $this->db->query("CALL sp_a_run ('SELECT','$payroll_ext_check_qry')");
$payroll_ext_check_rslt = $payroll_ext_check_info->result_array();
$payroll_ext_check_info->next_result();
if($payroll_ext_check_rslt[0]){
foreach($payroll_ext_check_rslt as $arr){
$payroll_exist_arr[$arr['prime_id']] = $arr['prime_id'];
}
}
return $payroll_exist_arr;
}
//FUNCTION FOR GET DYNAMICAL PAY STRUCTURE ADD COLUMN ARRAY FROM PAY ADD AND FILTER STRUCTURE SETTINGS
public function pay_add_filter_pick_column(){
$pay_add_filter_column_rslt = $this->pay_add_filter_col_setting();
$process = "filter";
$this->pay_add_filter_arr = $this->picklist_get_function($process,$pay_add_filter_column_rslt);
}
//excel excport
public function excel_export(){
$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);
}
$post_data = $this->input->post();
$personal_code = $post_data['personal_code'];
$wbs_element = $post_data['wbs_element'];
$project_id = $post_data['project_id'];
$position = $post_data['position'];
$network_id = $post_data['network_id'];
$filter_qry = "";
if($project_id){
$filter_qry = 'AND cw_sap_project.pro_id = "'.$project_id.'"';
}
if($wbs_element){
$filter_qry = 'AND cw_pay_structure.wbs_element = "'.$wbs_element.'"';
}
if($position){
$filter_qry = 'AND cw_pay_structure.position = "'.$position.'" AND cw_pay_structure.network_id = "'.$network_id.'"';
}
$pay_structure_qry = 'SELECT @a:=@a+1 s_no,cw_sap_personal_area.personal_name AS personal_code,cw_sap_wbs.wbs_desc AS wbs_element,cw_sap_project.pro_desc AS project_id,cw_sap_position.position_name AS POSITION,cw_sap_wbs.wbs_network_id AS network_id,cw_pay_structure.prime_pay_structure_id,cw_sap_activity.act_no AS activity_no,cw_sap_activity.act_desc AS activity_description,cw_category.category_name,cw_sap_wbs.wbs_id AS Wbs_code ,cw_sap_position.position_code AS position_code,from_date,to_date,gross,pf_gross,effective_date,cw_sap_professional_tax.ptax_name AS tax_location,cw_day_condition.day_condition AS day_condition,day_count,cw_pay_structure.pf_limit,cw_pay_structure.pf_exempt,cw_pay_structure.esi_exempt,cw_pay_structure.lwf_exempt FROM cw_pay_structure CROSS JOIN (SELECT @a:= 0) AS a INNER JOIN cw_day_condition ON cw_day_condition.prime_day_condition_id = cw_pay_structure.day_condition INNER JOIN cw_sap_professional_tax ON cw_sap_professional_tax.ptax_code = cw_pay_structure.tax_location INNER JOIN cw_category ON cw_category.prime_category_id = cw_pay_structure.category INNER JOIN cw_sap_activity ON cw_sap_activity.prime_sap_activity_id = cw_pay_structure.activity_no INNER JOIN cw_sap_personal_area ON cw_sap_personal_area.personal_code = cw_pay_structure.personal_code INNER JOIN cw_sap_position ON cw_sap_position.position_code = cw_pay_structure.position INNER JOIN cw_sap_wbs ON cw_pay_structure.wbs_element = cw_sap_wbs.wbs_id INNER JOIN cw_sap_project ON cw_sap_project.pro_id = cw_sap_wbs.wbs_project_id WHERE cw_sap_wbs.trans_status = 1 AND cw_pay_structure.trans_status = 1 AND cw_day_condition.trans_status = 1 AND cw_sap_personal_area.trans_status = 1 AND cw_sap_wbs.trans_status = 1 AND cw_sap_project.trans_status = 1 AND cw_sap_position.trans_status = 1 AND cw_pay_structure.personal_code = "'.$personal_code.'" '.$filter_qry.'';
$pay_structure_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_structure_qry')");
$pay_structure_rslt = $pay_structure_info->result_array();
$pay_structure_info->next_result();
if(!$pay_structure_rslt){
echo "No_Records_Found";
exit(0);
}
foreach($pay_structure_rslt as $arr){
$pay_structure_arr[$arr['prime_pay_structure_id']] = $arr;
}
$pay_structure_id_arr = array_column($pay_structure_rslt ?? [],'prime_pay_structure_id');
$pay_structure_id_arr = array_unique($pay_structure_id_arr ?? []);
$pay_structure_id_val = implode(',', $pay_structure_id_arr ?? []);
$pay_struct_line_qry = 'SELECT * FROM cw_pay_structure_line WHERE trans_status = 1 AND prime_pay_structure_id IN ('.$pay_structure_id_val.')';
$pay_struct_line_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_struct_line_qry')");
$pay_struct_line_rslt = $pay_struct_line_info->result_array();
$pay_struct_line_info->next_result();
foreach($pay_struct_line_rslt as $arr){
$pay_struct_line_rslts[$arr['hrms_field_name']][$arr['prime_pay_structure_id']] = $arr;
}
$select_columns = '*';
$table_name = 'cw_status_mode';
$table_join = '';
$table_where = ' cw_status_mode.trans_status = 1';
$status_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
foreach($status_rslt as $arr){
$status_rslt[$arr['prime_status_mode_id']] = $arr['status_mode_value'];
}
$pay_struct_line_qry = 'SELECT label_name,view_name,cw_wage_map_hrms.wage_name FROM `cw_payroll_formula` INNER JOIN cw_form_setting on cw_form_setting.label_name = cw_payroll_formula.out_column INNER JOIN cw_wage_map_hrms ON cw_wage_map_hrms.hrms_field_name = cw_form_setting.label_name WHERE cw_payroll_formula.order_by != 0 AND cw_payroll_formula.formula_mode = 1 AND cw_form_setting.paystructure_check = 1 AND cw_payroll_formula.trans_status = 1 AND cw_form_setting.trans_status = 1 ORDER BY order_by ASC';
$pay_struct_line_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_struct_line_qry')");
$pay_struct_line_view = $pay_struct_line_info->result_array();
$pay_struct_line_info->next_result();
foreach($pay_struct_line_view as $arr){
$pay_struct_line_arr_views[$arr['label_name']] = $arr;
}
$label_val_arr = array('amount','pf_applicable','esi_applicable','pt_applicable');
$export_arr = array();
foreach($pay_struct_line_rslt as $key => $pay_line_val){
foreach($label_val_arr as $label_val){
foreach($pay_struct_line_arr_views as $lab_key => $value){
$prime_pay_structure_id = $pay_line_val['prime_pay_structure_id'];
$applicable_val = $pay_struct_line_rslts[$lab_key][$prime_pay_structure_id][$label_val];
$component_label= $value['wage_name'];
$component_name = $component_label." ".$label_val;
if($label_val === "amount"){
$export_arr[$prime_pay_structure_id][$component_name] = $pay_struct_line_rslts[$lab_key][$prime_pay_structure_id]['amount'];
}else{
$export_arr[$prime_pay_structure_id][$component_name] = $status_rslt[$applicable_val];
}
}
}
}
//YES/NO REPLACING
$label_name_arr = array('pf_limit','pf_exempt','esi_exempt','lwf_exempt','pf_applicable','esi_applicable','pt_applicable');
foreach($pay_structure_arr as $key => &$val){
foreach($label_name_arr as $subkey => $label_name){
if($val[$label_name]){
$val[$label_name] = $status_rslt[$val[$label_name]];
}
}
}
unset($val);
$export_arr_data = array_replace_recursive($pay_structure_arr ?? [], $export_arr ?? []);
// $new_export_arr_data = array();
// foreach ($export_arr_data as $row) {
// unset($row['prime_pay_structure_id']);
// $new_export_arr_data[] = $row;
// }
// echo json_encode(array('success' => true,'message' => 'Records Found.!','view_rslt' => $header,'pay_rslt' => $new_export_arr_data));
$column_names = false;
foreach($export_arr_data as $row){
unset($row['prime_pay_structure_id']);
if(!$column_names) {
array_map( 'ucwords', str_replace( '_', ' ', $row ) );
echo implode("\t", array_map( 'ucwords', str_replace( '_', ' ', array_keys($row ?? []) ) ) ?? []) . "\n";
$column_names = true;
}
// The array_walk() function runs each array element in a user-defined function.
// array_walk($row, 'filterdatas');
// echo implode("\t", array_values($row)) . "\n";
echo implode("\t", str_replace( '^', '&', str_replace( '~', '"', str_replace( "`", "'", array_values($row ?? []) ) ) ) ?? []) . "\n";
}
fclose($output);
exit;
}
public function get_wbs(){
$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);
}
$search_term = $this->input->post_get('term');
$personal_code = $this->input->post('personal_code');
$project_id = $this->input->post('project_id');
$search_qry = 'SELECT DISTINCT wbs_id, wbs_desc FROM cw_sap_wbs INNER JOIN cw_pay_structure ON cw_pay_structure.wbs_element = cw_sap_wbs.wbs_id WHERE cw_sap_wbs.trans_status = 1 AND FIND_IN_SET("'.$personal_code.'", cw_sap_wbs.wbs_personal_area_id) AND (cw_sap_wbs.wbs_id LIKE CONCAT("%", "'.$search_term.'", "%") OR cw_sap_wbs.wbs_desc LIKE CONCAT("%", "'.$search_term.'", "%"))';
$search_info = $this->db->query("CALL sp_a_run ('SELECT','$search_qry')");
$search_rslt = $search_info->result();
$search_info->next_result();
if($search_rslt[0]){
echo json_encode(array('success' => true, 'search_rslt' => $search_rslt));
}else{
echo json_encode(array('success' => false, 'message' => "No Data Found..!"));
}
}
public function get_project(){
$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);
}
$search_term = $this->input->post_get('term');
$personal_code = $this->input->post_get('personal_code');
$proj_info = $this->db->query("CALL sp_sap_picks ('$search_term','$personal_code','project')");
$proj_rslt = $proj_info->result();
$proj_info->next_result();
if($proj_rslt[0]){
echo json_encode(array('success' => true, 'search_rslt' => $proj_rslt));
}else{
echo json_encode(array('success' => false, 'message' => "No Data Found..!"));
}
}
public function get_pick_data(){
$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);
}
$wbs_element = $this->input->post("wbs_element");
$pro_qry[] = array("return"=>"activity_info","qry"=>'SELECT cw_sap_activity.prime_sap_activity_id,cw_sap_activity.act_no,cw_sap_activity.act_desc FROM cw_sap_activity WHERE cw_sap_activity.act_wbs_id = "'.$wbs_element.'" and cw_sap_activity.trans_status = 1');
$pro_qry[] = array("return"=>"network_info","qry"=>'select cw_sap_wbs.wbs_id,cw_sap_wbs.wbs_network_id from cw_sap_wbs where trans_status = 1 and cw_sap_wbs.wbs_id = "'.$wbs_element.'"');
$pro_qry[] = array("return"=>"position_info","qry"=>'SELECT position_code,position_name FROM cw_sap_position
INNER JOIN cw_pay_structure ON cw_pay_structure.position = cw_sap_position.position_code WHERE cw_sap_position.trans_status = 1
AND cw_pay_structure.wbs_element="'.$wbs_element.'"');
$pick_data_rslt = $this->run_multi_qry($pro_qry);
if(!$pick_data_rslt->sts){
echo json_encode(array('success' => false, 'message' => "Please try After Sometime..!"));
}else{
$activity_rslt = $pick_data_rslt->rslt->activity_info;
$network_rslt = $pick_data_rslt->rslt->network_info;
$position_rslt = $pick_data_rslt->rslt->position_info;
echo json_encode(array('success' => true, 'activity_rslt' => $activity_rslt, 'network_rslt' => $network_rslt, 'position_rslt' => $position_rslt));
}
}
}
?>