File: /home/cafsindia/uds.cafsinfotech.in/application/controllers/Arrear_entry.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Arrear_entry extends Action_controller{
public function __construct(){
parent::__construct('arrear_entry');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$this->page_info();
$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;
//PROJECT ID GET BASED ON ARREAR INFO TABLE
$arr_pro_id_qry = 'select prime_sap_project_id,pro_id,pro_desc from cw_sap_project inner join cw_arr_info on cw_arr_info.project_id = cw_sap_project.pro_id where cw_sap_project.trans_status = 1 and cw_sap_project.pro_personal_area_id in ('.rtrim($this->logged_area_access,',').') and cw_arr_info.trans_status = 1 GROUP BY cw_arr_info.project_id';
$arr_pro_id_data = $this->db->query("CALL sp_a_run ('SELECT','$arr_pro_id_qry')");
$arr_pro_id_rslt = $arr_pro_id_data->result_array();
$arr_pro_id_data->next_result();
$arr_pro_id[''] = "---- Select Project----";
foreach($arr_pro_id_rslt as $for){
$prime_id = $for['prime_sap_project_id'];
$pro_id = $for['pro_id'];
$pro_desc = $for['pro_desc'];
$arr_pro_id[$pro_id] = $pro_id.' ~ '.$pro_desc;
}
$data['arr_project'] = $arr_pro_id;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//LOAD TABEL WITH FILTERS
public function search(){
//SEARCH INFO FUNCTION
$this->search_info();
$module_sts = (int)$this->module_sts;
if(!$module_sts){
echo json_encode(array('success' => FALSE, 'message' => "Search Info Query Process Error..!"));
exit(0);
}else{
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$order = $this->input->post('order');
$order_col = $this->input->post('columns');
$search = $this->input->post('search');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$order_col = $order_col[$column]['data'];
$search = trim($search['value']);
$search_query = str_replace("@SELECT@",$this->select_query,$this->base_query);
$search_result = array();
//ADDED BASIC,FILTER,COMMON QUERY HERE
$role_condition = "";
if($this->role_condition){
$role_condition = $this->role_condition;
}
$fliter_query = "";
//DR COMMAND CODE FOR WAITING TO ADD A NEW CHANGES
foreach($this->fliter_list as $fliter){
$label_id = $fliter->label_name;
$field_isdefault = (int)$fliter->field_isdefault;
$field_type = (int)$fliter->field_type;
$prime_form_id = $fliter->prime_form_id;
if($field_isdefault === 1){
$column_name = $this->prime_table .".$label_id";
$search_val = $this->input->post("$label_id");
if($search_val){
if($field_type === 4){
$search_val = date('Y-m-d',strtotime($search_val));
$fliter_query .= ' and '.$column_name.' = "'.$search_val.'"';
}else
if(($field_type === 5) || ($field_type === 7)){
$search_val = trim(implode('","',$search_val ?? []));
$fliter_query .= ' and '.$column_name.' in ("'.$search_val.'")';
}else
if($field_type === 9){
$search_id = 'filter_'.$label_id.'_hidden_'.$prime_form_id;
$search_val = $this->input->post("$search_id");
$fliter_query .= ' and '.$column_name.' in ("'.$search_val.'")';
}else
if($field_type === 13){
$search_val = date('Y-m-d H:i:s',strtotime($search_val));
$fliter_query .= ' and '.$column_name.' = "'.$search_val.'"';
}else{
$fliter_query .= ' and '.$column_name.' LIKE "'.$search_val.'%"';
}
}
}
}
$common_search = "";
if($search){
foreach($this->table_head as $setting){
$prime_form_id = $setting->prime_form_id;
$field_type = (int)$setting->field_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$pick_list_type = $setting->pick_list_type;
$input_view_type = (int)$setting->input_view_type;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$label_id = strtolower(str_replace(" ","_",$setting->label_name));
$field_isdefault = (int)$setting->field_isdefault;
if($field_isdefault === 1){
if(($input_view_type === 1) || ($input_view_type === 2)){
$search_label = "$this->prime_table.$label_id";
$search_val = "";
if($field_type === 4){ // having issues in date search
if(strtotime($search)){
$search_val = date('Y-m-d',strtotime($search));
$common_search .= ' or '. $search_label .' like "'.$search_val.'%"';
}
}else
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$result = array_filter($this->pick_list[$label_id]['array_list'] ?? [], function ($item) use ($search) {
if (stripos($item, $search) !== false) {
return true;
}
return false;
});
if($result){
$pick_key = implode('", "', array_keys($result ?? []) ?? []);
$common_search .= ' or '. $search_label .' in("'.$pick_key.'")';
}
}else{
$common_search .= ' or '. $search_label .' like "'.$search.'%"';
}
}
}
}
if($common_search){
$common_search = ltrim($common_search,' or ');
$common_search = " and ($common_search)";
$common_search = str_replace("(,","(",$common_search);
$common_search = str_replace("()","(0)",$common_search);
}
}
$count_all_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query = $count_all_query.' where '.$this->prime_table.'.trans_status = 1 '.$role_condition.$fliter_query.$common_search;
$search_query .= " where $this->prime_table.trans_status = 1 $role_condition $fliter_query $common_search";
$search_query .= " ORDER BY $order_col $order_sor";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_pro_qry = [];
$search_pro_qry[] = array("return"=>"total_count","qry"=>$count_all_query);
$search_pro_qry[] = array("return"=>"filtered_count","qry"=>$count_query);
$search_pro_qry[] = array("return"=>"search_result","qry"=>$search_query);
$search_info_rslt = $this->run_multi_qry($search_pro_qry);
$total_count = $search_info_rslt->rslt->total_count[0]->allcount;
$filtered_count = $search_info_rslt->rslt->filtered_count[0]->allcount;
$search_result = json_decode(json_encode($search_info_rslt->rslt->search_result),true);
if($search_result === null || $search_result === ''){
$search_result = [];
}
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
}
public function view($form_view_id=-1){
//VIEW INFO FUNCTION
$this->view_info($form_view_id);
$data['module_sts'] = (int)$this->module_sts;
//VIEW, FORM INPUT
$data['view_info'] = $this->view_info;
$data['form_info'] = $this->form_info;
$data['formula_result'] = $this->formula_result;//for label color change
$data['role_based_condition'] = $this->role_based_condition;//for label name readonly options
$data['all_pick'] = $this->pick_list;//all pick result
//DEPENDENT AUTO PICKLIST
$data['auto_pick'] = $this->depen_auto_list;//dependent auto result
$data['condition_list'] = $this->form_condition_list;
//VIEW DATA
$base_query = str_replace("@SELECT@",$this->view_select,$this->base_query);
$form_view_qry = $base_query .' where '.$this->prime_table.'.'.$this->prime_id.' = '.$form_view_id.' and '.$this->prime_table.'.trans_status = 1';
$row_view_qry = 'select * from cw_form_view_setting where prime_view_module_id = "'.$this->control_name.'" and form_view_type = "3" and trans_status = 1';
$view_pro_qry = [];
$view_pro_qry[] = array("return"=>"form_view","qry"=>$form_view_qry);
$view_pro_qry[] = array("return"=>"row_view_list","qry"=>$row_view_qry);
$view_info_rslt = $this->run_multi_qry($view_pro_qry);
$form_view_rslt = $view_info_rslt->rslt->form_view[0];
$row_view_rslt = $view_info_rslt->rslt->row_view_list;
$data['form_view'] = $form_view_rslt;
$row_view_list = array();
foreach($row_view_rslt as $view){
$prime_form_view_id = $view->prime_form_view_id;
$row_set_data = $this->get_row_set_data($prime_form_view_id,$form_view_id);
$row_view_list[$prime_form_view_id] = $row_set_data;
}
$data['row_view_list'] = $row_view_list;
//FOR DEPENDENT
$data['get_depend_prime_id'] = $this->get_depend_fun();
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/form",$data);
}
//SAVE MODEL DATA TO DATA BASE
public function save(){
//SAVE INFO FUNCTION
$this->save_info();
$module_sts = (int)$this->module_sts;
if(!$module_sts){
echo json_encode(array('success' => FALSE, 'message' => "Save Info Query Process Error...!"));
exit(0);
}else{
$unq_chk = array();
$prime_qry_key = "";
$prime_qry_value = "";
$prime_upd_query = "";
$cf_qry_key = "";
$cf_qry_value = "";
$cf_upd_query = "";
$cf_has = false;
//Decrypt Form id
$form_id = $this->input->post($this->prime_id);
if($form_id !== '-1'){ //Decryption
$decRslt = $this->cryptoDecrypt(base64_decode(urldecode($form_id)));
$form_id = (int)$decRslt['prime_id'];
if(!$form_id){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
}else{
$form_id = 0;
}
$form_post_data = array();
foreach($this->form_info as $setting){
$field_type = $setting->field_type;
$input_view_type = (int)$setting->input_view_type;
$label_id = strtolower(str_replace(" ","_",$setting->label_name));
$field_isdefault = $setting->field_isdefault;
$unique_field = (int)$setting->unique_field;
$view_name = $setting->view_name;
$date_type = $setting->date_type;
if((int)$field_type === 5){
$value = trim($this->input->post($label_id));
$pick_array[$label_id] = $value;
}else
if((int)$field_type === 7){
$multi_name = $label_id."[]";
$value = trim(implode(",",$this->input->post($multi_name) ?? []));
}else{
$value = trim($this->input->post($label_id));
}
if((int)$field_type === 4){
if((int)$date_type === 1){
$value = date('Y-m-d',strtotime($value));
}else{
$value = $value;
}
}else
if((int)$field_type === 13){
$value = date('Y-m-d H:i:s',strtotime($value));
}else
if((int)$field_type === 8){//textbox only
$value = str_replace('"',"~",$value);
$value = str_replace("'","`",$value);
$value = str_replace("&","^",$value);
}else
if((int)$field_type === 10){//File Upload
$value = $value;
$remove_upload_fname = "old_".$label_id;
$remove_upload_file = $this->input->post($remove_upload_fname);
if($value !== $remove_upload_file){
unlink($remove_upload_file);
}
}
if(($input_view_type === 1) || ($input_view_type === 2)){
if((int)$field_isdefault === 1){
$prime_qry_key .= $label_id.",";
$prime_qry_value .= '"'.$value.'",';
$prime_upd_query .= $label_id.' = "'.$value.'",';
if($unique_field === 1){
$prime_unq_chk = $label_id.'= "'.$value.'"';
$query = "select count(*) as rslt_count from $this->prime_table where $this->prime_id != $form_id and $prime_unq_chk";
$unq_chk[] = array('label_id'=>$label_id,'view_name'=>$view_name,'query'=>$query,);
}
}
}
}
$rslt_count = 0;
$can_process = array();
foreach($unq_chk as $unq_rslt){
$query = $unq_rslt['query'];
$label_id = $unq_rslt['label_id'];
$view_name = $unq_rslt['view_name'];
$unq_info = $this->db->query("CALL sp_a_run ('RUN','$query')");
$unq_result = $unq_info->result();
$unq_info->next_result();
if($unq_result){
$rslt_count = (int)$unq_result[0]->rslt_count;
if($rslt_count !== 0){
$can_process[] = $view_name." already exist";
}
}
}
if(count($can_process ?? []) > 0){
$can_process = array_values($can_process ?? []);
$can_process = implode(",<br/>", $can_process ?? []);
echo json_encode(array('success' => false, 'message' => $can_process,));
}else{
$created_on = date("Y-m-d h:i:s");
if((int)$form_id === 0){
$prime_qry_key .= "trans_created_by,trans_created_date";
$prime_qry_value .= '"'.$this->logged_id.'",'.'"'.$created_on.'"';
$prime_insert_query = "insert into $this->prime_table ($prime_qry_key) values ($prime_qry_value)";
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$send_mail_status = "";
if((int)$this->email_count > 0){
$send_mail_status = $this->dynamic_mail_sent($insert_id,$pick_array,$previous_data);
}
echo json_encode(array('success' => TRUE, 'message' => "Successfully added $send_mail_status", 'insert_id' => $insert_id));
}else{
$prime_upd_query .= 'trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$prime_update_query = 'UPDATE '. $this->prime_table .' SET '. $prime_upd_query .' WHERE '. $this->prime_id .' = "'. $form_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')");
$send_mail_status = "";
if((int)$this->email_count > 0){
$send_mail_status = $this->dynamic_mail_sent($form_id,$pick_array,$previous_data);
}
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated $send_mail_status",'insert_id' => $form_id));
}
}
}
}
//UPDATE STATUS TO DELETE IN MODULE PRIMARY TABLE
public function delete(){
$delete_ids = implode(",",$this->input->post('delete_ids') ?? []);
$can_process = TRUE;
$delete_status = FALSE;
if($this->check_delete_status()){
$delete_status = TRUE;
$check_table_query = 'SELECT GROUP_CONCAT(prime_module_id) as prime_module_id,GROUP_CONCAT(label_name) as label_name from cw_form_setting WHERE pick_table = "'. $this->prime_table .'" and trans_status = 1 ';
$check_table_info = $this->db->query("CALL sp_a_run ('SELECT','$check_table_query')");
$check_table_rlst = $check_table_info->row();
$check_table_info->next_result();
if($check_table_rlst->prime_module_id){
$prime_module_id = explode(",",$check_table_rlst->prime_module_id ?? "");
$label_name = explode(",",$check_table_rlst->label_name ?? "");
$i = 0;
$select_table = '';
$select_label = '';
$select_trans_status = '';
$select_where = '';
foreach($prime_module_id as $check_modules){
$table_name = "cw_".$check_modules;
$table_rename = $table_name."_$i";
$select_table .= "$table_rename.$label_name[$i],";
$select_label .= " $table_name $table_rename,";
if((int)$i === 0){
$select_trans_status .= "( $table_rename.trans_status = 1";
$select_where .= " and ($table_rename.$label_name[$i] in ($delete_ids)";
}else{
$select_trans_status .= " and $table_rename.trans_status = 1";
$select_where .= " or $table_rename.$label_name[$i] in ($delete_ids)";
}
$i++;
}
$select_trans_status .= ")";
$select_where .= ")";
$select_table = rtrim($select_table,',');
$select_label = rtrim($select_label,',');
$check_module_query .= 'SELECT '.$select_table.' from '.$select_label.' WHERE '.$select_trans_status.' '.$select_where.' LIMIT 0,1';
$check_module_info = $this->db->query("CALL sp_a_run ('SELECT','$check_module_query')");
$values_count = $check_module_info->num_rows();
$check_module_info->next_result();
if((int)$values_count > 0){
$can_process = False;
$delete_status = False;
}
}
if($delete_status){
$delete_query = 'DELETE FROM '. $this->prime_table .' WHERE '. $this->prime_id .' in ('. $delete_ids .')';
if($this->db->query("CALL sp_a_run ('RUN','$delete_query')")){
$row_set_query = 'SELECT form_view_label_name from cw_form_view_setting where form_view_type = "3" and prime_view_module_id = "'. $this->control_name .'" and trans_status = 1';
$row_set_info = $this->db->query("CALL sp_a_run ('SELECT','$row_set_query')");
$row_count = (int)$row_set_info->num_rows();
$row_set_info->next_result();
if($row_count !== 0){
$row_set_result = $row_set_info->result();
$delete_table_name = '';
$delete_table_condition = '';
foreach($row_set_result as $row_set){
$row_set_table_name = "cw_".$this->control_name."_".$row_set->form_view_label_name;
$delete_table_name .= "$row_set_table_name,";
$delete_table_condition .= " $row_set_table_name.$this->prime_id in ('$delete_ids') and";
}
$delete_table_name = rtrim($delete_table_name,',');
$delete_table_condition = rtrim($delete_table_condition,'and');
$delete_row_set_query = 'DELETE FROM '. $delete_table_name .' WHERE '. $delete_table_condition.'';
$this->db->query("CALL sp_a_run ('RUN','$delete_row_set_query')");
}
$can_process = False;
}
}
}
if($can_process){
$created_on = date("Y-m-d h:i:s");
$prime_upd_query .= 'trans_deleted_by = "'. $this->logged_id .'",trans_deleted_date = "'.$created_on.'"';
$prime_update_query = 'UPDATE '. $this->prime_table .' SET trans_status = 0,'. $prime_upd_query .' WHERE '. $this->prime_id .' in ('. $delete_ids .')';
if($this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')")){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to delete"));
}
}else
if($delete_status){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}else{
$modules = ucwords($check_table_rlst->prime_module_id);
echo json_encode(array('success' => FALSE, 'message' => "Unable to delete, This value is already used in $table_names modules"));
}
}
//CHECK UNIQUE FIELD STATUS
public function check_delete_status(){
$check_delete_query = 'SELECT GROUP_CONCAT(unique_field) as unique_field from cw_form_setting WHERE prime_module_id = "'. $this->control_name .'" and trans_status = 1 ';
$check_delete_info = $this->db->query("CALL sp_a_run ('SELECT','$check_delete_query')");
$check_delete_rlst = $check_delete_info->row();
$check_delete_info->next_result();
$unique_info = explode(",",$check_delete_rlst->unique_field ?? "");
if(in_array('1', $unique_info)){
return TRUE;
}else{
return FALSE;
}
}
//UPDATE STATUS TO DELETE FOR UPLOAD FILES or DOCUMENTS
public function remove_file(){
$prime_id_val = $this->input->post('prime_id_val');
$is_defult = (int)$this->input->post('is_defult');
$input_name = $this->input->post('input_name');
$input_val = $this->input->post('input_val');
if($input_val){
chmod($input_val, 0777);
unlink($input_val);
}
$table_name = '';
if($is_defult === 1){
$table_name = $this->prime_table;
}else
if($is_defult === 2){
$table_name = $this->cf_table;
}
if($table_name){
$created_on = date("Y-m-d h:i:s");
$set_query = $input_name .' = "" ,trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$update_query = 'UPDATE '.$table_name .' SET '. $set_query .' WHERE '. $this->prime_id .' = "'. $prime_id_val .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to process your request"));
}
}
//IMPORT FILE VIEW INFORMATION
public function import(){
$data['module_id'] = $this->control_name;
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "'.$this->control_name.'" and trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_format_drop[""] = "---- Excel Format ----";
foreach($excel_result as $excel){
$prime_excel_format_id = $excel->prime_excel_format_id;
$excel_name = $excel->excel_name;
$excel_format_drop[$prime_excel_format_id] = $excel_name;
}
$data['excel_format_drop'] = $excel_format_drop;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/import",$data);
}
//--------------------------- DR STATIC CODE START ---------------------- ----------------------------
//DR GET POSTION FROM WBS AND FROM AND TO DATE BASED FOR NEW ENTRY TABLE 5OCT22 START
public function get_position(){
$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');
$arr_from_date = $this->input->post('arr_from_date');
$arr_to_date = $this->input->post('arr_to_date');
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
//Get Position Details from cw_transactions based on date and wbs
$trans_pay_pos_qry = 'SELECT cw_sap_position.position_code,cw_sap_position.position_name,cw_sap_position.prime_sap_position_id,a.gross,a.prime_pay_structure_id FROM cw_pay_structure a inner join cw_transactions_fms on cw_transactions_fms.pay_struct_id = a.prime_pay_structure_id INNER JOIN cw_sap_position on a.position = cw_sap_position.position_code WHERE a.trans_status = 1 and a.wbs_element = "'.$wbs_element.'" and date_format(str_to_date(CONCAT("01-",cw_transactions_fms.transactions_month), "%d-%m-%Y") , "%Y-%m") >= "'.$arr_from_date.'" and date_format(str_to_date(CONCAT("01-",cw_transactions_fms.transactions_month), "%d-%m-%Y") , "%Y-%m") <= "'.$arr_to_date.'" GROUP BY prime_pay_structure_id';
$trans_pay_pos_info = $this->db->query("CALL sp_a_run ('SELECT','$trans_pay_pos_qry')");
$trans_pay_pos_rslt = $trans_pay_pos_info->result_array();
$trans_pay_pos_info->next_result();
$trans_pay_arr = array();
$pos_list = "<option value = ''>----Select Position ----</option>";
foreach($trans_pay_pos_rslt as $for){
$prime_id = $for['prime_sap_position_id'];
$position = $for['position_code'];
$position_name = $for['position_name'];
$gross = $for['gross'];
$pay_id = $for['prime_pay_structure_id'];
$pos_list .= "<option data-value = '$position' value = '$position ~ $gross' data-id = '$pay_id'> $position_name</option>";
}
echo $pos_list;
}
//DR GET REVISED POSTION FROM TRANSACTION AND EMPLOYEE MASTER AND FROM AND TO DATE BASED FOR NEW ENTRY TABLE 5OCT22 START
public function get_rev_position(){
$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');
$position = $this->input->post('position');
$arr_from_date = date($this->input->post('arr_from_date')."-01");
$arr_to_date = date($this->input->post('arr_to_date')."-01");
$project_id = $this->input->post('project');
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
# Position records in arrear info either pending or completed should not show.
//as discussed with porul #and arr_status = 1
//As discussed with porul on 13sep2024 - 2 different promotion from the different position to particular position not working so checked the position AND position = "'.$position.'".
$pos_qry = 'SELECT distinct rev_position FROM cw_arr_info WHERE trans_status = 1 AND wbs_element = "'.$wbs_element.'" AND position = "'.$position.'" AND project_id = "'.$project_id.'" AND arr_from_date = "'.$from_date.'" AND arr_to_date = "'.$to_date.'" '; #GROUP BY rev_position
$pos_data = $this->db->query("CALL sp_a_run ('SELECT','$pos_qry')");
$pos_rslt = $pos_data->result_array();
$pos_data->next_result();
$positions = array_column($pos_rslt ?? [], 'rev_position');
$positions = array_map(function($value){
return '"' . $value . '"';
}, $positions ?? []);
$revpos_rslt = implode(',', $positions ?? []);
if(!$revpos_rslt){
$revpos_rslt = '""';
}
// echo $revpos_rslt; die;
//QUERY FOR GET A SAME WBS EMPLOYEES SAME or Diff Position
$rev_pos_same_wbs_qry = 'SELECT DISTINCT cw_sap_position.position_code,cw_sap_position.position_name,cw_pay_structure.wbs_element,cw_sap_position.prime_sap_position_id,cw_pay_structure.prime_pay_structure_id,gross FROM cw_transactions_fms inner join cw_employees on cw_employees.employee_code = cw_transactions_fms.employee_code and cw_transactions_fms.project_id = cw_employees.project_id inner join cw_pay_structure on (cw_employees.wbs_element = cw_pay_structure.wbs_element and cw_employees.position = cw_pay_structure.position) inner join cw_sap_position on cw_pay_structure.position = cw_sap_position.position_code WHERE cw_transactions_fms.wbs_element = "'.$wbs_element.'" and cw_transactions_fms.position = "'.$position.'" and cw_employees.position not in('.$revpos_rslt.') and date_format(str_to_date(CONCAT("01-",cw_transactions_fms.process_month), "%d-%m-%Y") , "%Y-%m-%d") >= "'.$arr_from_date.'" and date_format(str_to_date(CONCAT("01-",cw_transactions_fms.process_month), "%d-%m-%Y") , "%Y-%m-%d") <= "'.$arr_to_date.'" and cw_pay_structure.prime_pay_structure_id != cw_transactions_fms.pay_struct_id and cw_employees.trans_status = 1 and cw_transactions_fms.trans_status = 1 and cw_sap_position.trans_status = 1'; // GROUP BY cw_employees.position
// and cw_pay_structure.status = 1
// cw_transactions_fms.employee_code,cw_pay_structure.personal_code,cw_pay_structure.wbs_element,cw_pay_structure.activity_no,cw_pay_structure.gross As discussed with porul on 8th mar2024
$rev_pos_same_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$rev_pos_same_wbs_qry')");
$rev_pos_same_wbs_rslt = $rev_pos_same_wbs_info->result_array();
$rev_pos_same_wbs_info->next_result();
#and cw_employees.wbs_element = "'.$wbs_element.'"
$rev_pos_arr = [];
foreach($rev_pos_same_wbs_rslt as $for){
$prime_id = $for['prime_sap_position_id'];
$revposition = $for['position_code'];
$position_name = $for['position_name'];
$paywbs = $for['wbs_element'];
$gross = $for['gross'];
$pay_id = $for['prime_pay_structure_id'];
$key = "$revposition ~ $paywbs"; //~ $gross
//move to array
$rev_pos_arr[$key]['prime_position_id'] = $prime_id;
$rev_pos_arr[$key]['position_code'] = $revposition;
$rev_pos_arr[$key]['position_name'] = $position_name;
$rev_pos_arr[$key]['gross'] = $gross;
$rev_pos_arr[$key]['pay_id'] = $pay_id;
}
//QUERY FOR GET A DIFFERENT WBS EMPLOYEES BASED POSITION
$rev_pos_diff_wbs_qry = 'SELECT DISTINCT cw_sap_position.position_code,cw_sap_position.position_name,cw_pay_structure.wbs_element,cw_sap_position.prime_sap_position_id,cw_pay_structure.prime_pay_structure_id,gross FROM cw_transactions_fms inner join cw_employees on cw_employees.employee_code = cw_transactions_fms.employee_code and cw_transactions_fms.project_id = cw_employees.project_id inner join cw_pay_structure on (cw_employees.wbs_element = cw_pay_structure.wbs_element and cw_employees.position = cw_pay_structure.position) inner join cw_sap_position on cw_pay_structure.position = cw_sap_position.position_code WHERE cw_transactions_fms.wbs_element = "'.$wbs_element.'" and cw_transactions_fms.position = "'.$position.'" and cw_employees.position not in('.$revpos_rslt.') and date_format(str_to_date(CONCAT("01-",cw_transactions_fms.process_month), "%d-%m-%Y") , "%Y-%m-%d") >= "'.$arr_from_date.'" and date_format(str_to_date(CONCAT("01-",cw_transactions_fms.process_month), "%d-%m-%Y") , "%Y-%m-%d") <= "'.$arr_to_date.'" and cw_employees.wbs_element != "'.$wbs_element.'" and cw_employees.trans_status = 1 and cw_transactions_fms.trans_status = 1 and cw_sap_position.trans_status = 1 '; //GROUP BY cw_transactions_fms.employee_code,cw_pay_structure.personal_code,cw_pay_structure.wbs_element,cw_pay_structure.position,cw_pay_structure.activity_no,cw_pay_structure.gross
// and cw_pay_structure.status = 1
#Removed as discussed with porul on 24dec2023 - revised posision can be diff salary
$rev_pos_diff_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$rev_pos_diff_wbs_qry')");
$rev_pos_diff_wbs_rslt = $rev_pos_diff_wbs_info->result_array();
$rev_pos_diff_wbs_info->next_result();
foreach($rev_pos_diff_wbs_rslt as $for){
$prime_id = $for['prime_sap_position_id'];
$revdposition = $for['position_code'];
$position_name = $for['position_name'];
$rev_wbs = $for['wbs_element'];
$gross = $for['gross'];
$pay_id = $for['prime_pay_structure_id'];
$key = "$revdposition ~ $rev_wbs"; //~ $gross
//move to array
$rev_pos_arr[$key]['prime_position_id'] = $prime_id;
$rev_pos_arr[$key]['position_code'] = $revdposition;
$rev_pos_arr[$key]['position_name'] = $position_name;
$rev_pos_arr[$key]['gross'] = $gross;
$rev_pos_arr[$key]['pay_id'] = $pay_id;
}
$pos_list = '';
foreach($rev_pos_arr as $key => $for){
$prime_id = $for['prime_position_id'];
$position_code = $for['position_code'];
$position_name = $for['position_name'];
$gross = $for['gross'];
$pay_id = $for['pay_id'];
$pos_list .= "<option data-value = '$position_code' value = '$key' data-id = '$pay_id'>$position_name</option>";
//$position_name
}
//Check Any Employees Transfered from One project to Another
$transfer_qry = 'SELECT cw_employees.employee_code FROM cw_transactions_fms inner join cw_employees on cw_employees.employee_code = cw_transactions_fms.employee_code WHERE cw_transactions_fms.wbs_element = "'.$wbs_element.'" and cw_transactions_fms.position = "'.$position.'" and cw_employees.position not in('.$revpos_rslt.') and date_format(str_to_date(CONCAT("01-",cw_transactions_fms.process_month), "%d-%m-%Y") , "%Y-%m-%d") >= "'.$arr_from_date.'" and date_format(str_to_date(CONCAT("01-",cw_transactions_fms.process_month), "%d-%m-%Y") , "%Y-%m-%d") <= "'.$arr_to_date.'" and cw_employees.project_id != cw_transactions_fms.project_id and cw_employees.trans_status = 1 and cw_transactions_fms.trans_status = 1 GROUP BY cw_transactions_fms.employee_code';
$transfer_info = $this->db->query("CALL sp_a_run ('SELECT','$transfer_qry')");
$transfer_rslt = $transfer_info->result_array();
$transfer_info->next_result();
if((int)count($transfer_rslt ?? []) > 0){
$pos_list .= "<option data-value = 'transfer' value = 'Transfer' data-id = '0'> Transfer </option>";
}
echo $pos_list;
}
public function get_pay_position(){
$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');
$position = $this->input->post('position');
$arr_from_date = $this->input->post('arr_from_date');
$prev_pay_id = $this->input->post('prev_pay_id');
$arr_from_date = date("Y-m-01",strtotime("01-" . $arr_from_date));
$act_pay_pos_qry = 'SELECT cw_sap_position.position_code,cw_sap_position.position_name,cw_sap_position.prime_sap_position_id,a.wbs_element,a.gross,a.prime_pay_structure_id FROM cw_pay_structure a INNER JOIN cw_sap_position on a.position = cw_sap_position.position_code WHERE a.trans_status = 1 and wbs_element = "'.$wbs_element.'" and position = "'.$position.'" and prime_pay_structure_id != "'.$prev_pay_id.'" and a.from_date >= "'.$arr_from_date.'" ';
//and prime_pay_structure_id != "'.$prev_pay_id.'"
$act_pay_pos_info = $this->db->query("CALL sp_a_run ('SELECT','$act_pay_pos_qry')");
$act_pay_pos_rslt = $act_pay_pos_info->result_array();
$act_pay_pos_info->next_result();
$pos_list = "";
foreach($act_pay_pos_rslt as $for){
$position = $for['position_code'];
$position_name = $for['position_name'];
$paywbs = $for['wbs_element'];
$gross = $for['gross'];
$pay_id = $for['prime_pay_structure_id'];
$pos_list .= "<option data-value = '$position' value = '$position ~ $gross ~ $paywbs ' data-id = '$pay_id'>$position_name</option>";
}
echo $pos_list;
}
//FUNCTION FOR NEW ENTRY TABLE SAVE ALL ROW AND COLUMN IN TABLE 5OCT22
public function save_all(){
$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);
}
$json_arr_arr = json_decode($this->input->post('json_arr_obj'),true);
$created_on = date("Y-m-d H:i:s");
//ARREAR DETAILS GET FROM TABLE
$err_row_arr = array();
$ext_row_arr = array();
$ins_key = '';
$ins_val = '';
$search_val = '';
$row = '';
$count = '';
$arr_info_qry = 'SELECT * FROM cw_arr_info WHERE trans_status = 1 ';
$arr_info_data = $this->db->query("CALL sp_a_run ('SELECT','$arr_info_qry')");
$arr_info_rslt = $arr_info_data->result_array();
$arr_info_data->next_result();
$exist_chk_arr = array();
$arr_data = array();
#Retrieving arr_info data -> NB[19-03-2024]
foreach($arr_info_rslt as $value){
$project_id = $value['project_id'];
$wbs_element = $value['wbs_element'];
$position = $value['prev_pay'];
$rev_position = $value['rev_pay'];
$pay_position = $value['arr_pay'];
$arr_status = $value['arr_status'];
$arr_data[$project_id][$wbs_element][$position][$rev_position] = $value;
}
#Same Data shouldnt allow to submit.
foreach($json_arr_arr as $key => $value){
$row = $key+1;
$project_id = $value['project_id'];
$wbs_element = $value['wbs_element'];
$from_date = date('Y-m-d', strtotime('01-'.$value['arr_from_date']));
$to_date = date('Y-m-d', strtotime('01-'.$value['arr_to_date']));
$arr_from_date = $value['arr_from_date'];
$arr_to_date = $value['arr_to_date'];
$position = $value['position'];
$rev_position = $value['rev_position'];
$pay_struct_position = $value['pay_struct_position'];
$prev_pay = $value['prev_pay'];
$rev_pay = $value['rev_pay'];
$arr_pay = $value['arr_pay'];
$arr_pf_exc = $value['arr_pf_exc'];
$arr_esi_exc = $value['arr_esi_exc'];
$arr_ot_exc = $value['arr_ot_exc'];
# Check for duplicates within the table itself.
$cur_arr = array('arr_from_date'=>$arr_from_date,'arr_to_date'=>$arr_to_date,'project_id'=>$project_id,'wbs_element'=>$wbs_element,'prev_pay'=>$prev_pay,'rev_pay'=>$rev_pay,'arr_pay'=>$arr_pay);
$exist_chk_arr[]= $cur_arr;
# For insertion.
$ins_val .= '"'.$project_id.'","'.$wbs_element.'","'.$arr_from_date.'","'.$arr_to_date.'","'.$position.'","'.$rev_position.'","'.$pay_struct_position.'","'.$prev_pay.'","'.$rev_pay.'","'.$arr_pay.'","'.$arr_pf_exc.'","'.$arr_esi_exc.'","'.$arr_ot_exc.'","'.$this->logged_id.'",'.'"'.$created_on.'"),(';
# Retrieve the data from arr info.
$data = $arr_data[$project_id][$wbs_element][$prev_pay][$rev_pay];
if($data){
$arr_from = date('Y-m-d',strtotime('01-'.$data['arr_from_date']));
$arr_to = date('Y-m-d',strtotime('01-'.$data['arr_to_date']));
$arr_sts = (int)$data['arr_status'];
# If dates fall within the specified range and 'arr_status' is not equal to 1
if($arr_from >= $from_date && $arr_to <= $to_date && $arr_sts !== 1 && $arr_sts !== 3){
array_push($err_row_arr ?? [],$row);
}
}
}
#Check for duplicates within the table itself.
foreach($json_arr_arr as $key => $value){
$row = $key + 1;
$duplicate = false;
$prev_pay = $value['prev_pay'];
$rev_pay = $value['rev_pay'];
$arr_pay = $value['arr_pay'];
$project_id = $value['project_id'];
$wbs_element = $value['wbs_element'];
$arr_from_date = $value['arr_from_date'];
$arr_to_date = $value['arr_to_date'];
// Count occurrences of the key in $exist_chk_arr
$count_occurrences = 0;
foreach($exist_chk_arr as $exist){
if($exist['project_id'] === $project_id && $exist['wbs_element'] === $wbs_element && $exist['arr_from_date'] === $arr_from_date && $exist['arr_to_date'] === $arr_to_date && $exist['prev_pay'] === $prev_pay && $exist['rev_pay'] === $rev_pay && $exist['arr_pay'] === $arr_pay){
$count_occurrences++;
}
}
// If the count is greater than 1, it's a duplicate.
if($count_occurrences > 1){
$duplicate = true;
}
if($duplicate){
array_push($ext_row_arr ?? [], $row);
}
}
$exist_row_count = count($ext_row_arr ?? []);
$error_row_count = count($err_row_arr ?? []);
if($exist_row_count){
echo json_encode(array('success' => FALSE, 'sts' => 1, 'message' => "Duplicate Table row Added",'error_row_col' => $ext_row_arr));
}elseif($error_row_count){
echo json_encode(array('success' => FALSE, 'sts' => 2, 'message' => "Any one of the selected month arrear for this position pending in Pre-Audit", 'error_row_col' => $err_row_arr));
}else{
if($ins_val){
$ins_val = rtrim($ins_val,"),(");
}
$insert_query = 'INSERT INTO cw_arr_info (project_id,wbs_element,arr_from_date,arr_to_date,position,rev_position,pay_struct_position,prev_pay,rev_pay,arr_pay,pf_exclude,esi_exclude,ot_exclude,trans_created_by,trans_created_date) VALUES ('.$ins_val.')';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = (int)$insert_result[0]->ins_id;
if($insert_id){
echo json_encode(array('success' => TRUE, 'message' => "Successfully added"));
}
}
}
//------------------------- SEARCH ARRER ENTRY PROCESS 110CT22 START -------------------------------------------
//DR GET FROM WBS AND TO FROM PROJECT BASED FOR SEARCH ENTRY 11OCT22 START
public function get_arr_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);
}
$project_id = $this->input->post('project_id');
//pay structure select qry
$get_wbs_qry = 'SELECT DISTINCT cw_sap_wbs.wbs_id,cw_sap_wbs.prime_sap_wbs_id,cw_sap_wbs.wbs_desc FROM cw_arr_info INNER JOIN cw_sap_wbs ON cw_sap_wbs.wbs_id = cw_arr_info.wbs_element WHERE cw_arr_info.project_id = "'.$project_id.'" and cw_arr_info.trans_status = 1 and cw_sap_wbs.trans_status = 1 AND cw_arr_info.project_id = "'.$project_id.'" '; //group by cw_arr_info.wbs_element
$get_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$get_wbs_qry')");
$get_wbs_rslt = $get_wbs_info->result_array();
$get_wbs_info->next_result();
$wbs_list = "<option value = ''>----Select Project ----</option>";
foreach($get_wbs_rslt as $for){
$prime_id = $for['prime_sap_wbs_id'];
$wbs_id = $for['wbs_id'];
$wbs_desc = $for['wbs_desc'];
$wbs_list .= "<option value = '$wbs_id'> $wbs_id ~ $wbs_desc</option>";
}
echo $wbs_list;
}
//DR GET FROM WBS AND TO FROM PROJECT BASED FOR SEARCH ENTRY 11OCT22 END
//FUNCTION FOR GET A ARREAR INFO TABLE DATA 11OCT22 START
public function arr_search_entry(){
$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);
}
//FUNCTION FOR GET A PICKLIST ARRAY
$picklist_arr = $this->picklist_data_function();
$status_arr = array(1 => "Yes",2 => "No");
$arr_project_id = $this->input->post('project_id');
$arr_from_wbs = $this->input->post('from_wbs');
$arr_to_wbs = $this->input->post('to_wbs');
$arr_position = $this->input->post('position');
$arr_status = $this->input->post('arr_status');
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
if($from_date){
$arr_from_date = $this->convertToMonthYear($from_date);
}
if($to_date){
$arr_to_date = $this->convertToMonthYear($to_date);
}
if($arr_from_date){
$arr_from_date = date('Y-m',strtotime('01-'.$arr_from_date));
}
if($arr_to_date){
$arr_to_date = date('Y-m',strtotime('01-'.$arr_to_date));
}
$wbs_where_qry = '';
$bet_wbs_where_qry = '';
$arr_where_qry = '';
$tr_line = '';
if($arr_project_id){
$arr_where_qry .= ' and cw_arr_info.project_id = "'.$arr_project_id.'"';
}
if($arr_from_date){
$arr_where_qry .= ' and date_format(str_to_date(CONCAT("01-",cw_arr_info.arr_from_date), "%d-%m-%Y") , "%Y-%m") = "'.$arr_from_date.'"';
}
if($arr_to_date){
$arr_where_qry .= ' and date_format(str_to_date(CONCAT("01-",cw_arr_info.arr_to_date), "%d-%m-%Y") , "%Y-%m") = "'.$arr_to_date.'"';
}
if($arr_position){
$arr_where_qry .= ' and cw_arr_info.position = "'.$arr_position.'"';
}
if($arr_status){
$arr_where_qry .= ' and cw_arr_info.arr_status = "'.$arr_status.'"';
}
if($arr_from_wbs){
if($arr_to_wbs){
$wbs_where_qry = '(cw_sap_wbs.wbs_id = "'.$arr_from_wbs.'" or cw_sap_wbs.wbs_id = "'.$arr_to_wbs.'")';
}else{
$wbs_where_qry = 'cw_sap_wbs.wbs_id = "'.$arr_from_wbs.'"';
}
if($wbs_where_qry){
$get_wbs_qry = 'SELECT GROUP_CONCAT(DISTINCT cw_sap_wbs.prime_sap_wbs_id) as wbs_id FROM cw_sap_wbs WHERE cw_sap_wbs.trans_status = 1 and '.$wbs_where_qry;
$get_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$get_wbs_qry')");
$get_wbs_rslt = $get_wbs_info->result();
$get_wbs_info->next_result();
$get_wbs_id = $get_wbs_rslt[0]->wbs_id;
if(strlen($get_wbs_id) === 1){
$bet_wbs_where_qry = 'cw_sap_wbs.prime_sap_wbs_id = "'.$get_wbs_id.'"';
}else{
$get_wbs_arr = explode(',',$get_wbs_id ?? "");
$min_wbs_id = min($get_wbs_arr);
$max_wbs_id = max($get_wbs_arr);
$bet_wbs_where_qry = 'cw_sap_wbs.prime_sap_wbs_id >= "'.$min_wbs_id.'" and cw_sap_wbs.prime_sap_wbs_id <= "'.$max_wbs_id.'"';
}
if($bet_wbs_where_qry){
$bet_wbs_qry = 'SELECT GROUP_CONCAT(DISTINCT cw_sap_wbs.wbs_id) as wbs_id FROM cw_sap_wbs WHERE cw_sap_wbs.trans_status = 1 and '.$bet_wbs_where_qry;
$bet_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$bet_wbs_qry')");
$bet_wbs_rslt = $bet_wbs_info->result();
$bet_wbs_info->next_result();
$arr_wbs_id = implode('","',explode(',',$bet_wbs_rslt[0]->wbs_id ?? "") ?? []);
if($arr_wbs_id){
$arr_where_qry .= ' and cw_arr_info.wbs_element in ("'.$arr_wbs_id.'")';
}
}
}
}
if($arr_where_qry){
# For Entry status based checkbox hidden/show.
$entry_qry = 'SELECT * FROM cw_arr_transactions WHERE trans_status = 1';
$entry_info = $this->db->query("CALL sp_a_run ('SELECT','$entry_qry')");
$entry_rslt = $entry_info->result_array();
$entry_info->next_result();
$entry_array = [];
foreach($entry_rslt as $val){
$prime_arr_info = $val['prime_arr_info_id'];
$entry_array[$prime_arr_info] = $val;
}
$arr_info_qry = 'SELECT DISTINCT cw_arr_info.prime_arr_info_id,pos.gross AS pos_gross,rev.gross AS rev_gross,pay.gross AS pay_gross,pos.wbs_element AS pos_wbs,rev.wbs_element AS rev_wbs,pay.wbs_element AS pay_wbs,cw_arr_info.project_id,cw_arr_info.wbs_element,arr_from_date,arr_to_date,cw_arr_info.position,cw_arr_info.rev_position,cw_arr_info.pay_struct_position,pf_exclude,esi_exclude,ot_exclude,cw_arr_info.arr_status,cw_arr_info.trans_created_date FROM cw_arr_info JOIN cw_pay_structure pos ON pos.prime_pay_structure_id = prev_pay LEFT JOIN cw_pay_structure rev ON rev.prime_pay_structure_id = rev_pay JOIN cw_pay_structure pay ON pay.prime_pay_structure_id = arr_pay WHERE cw_arr_info.trans_status = 1 '.$arr_where_qry.' '; //GROUP BY cw_arr_info.prime_arr_info_id
$arr_info_data = $this->db->query("CALL sp_a_run ('SELECT','$arr_info_qry')");
$arr_info_rslt = $arr_info_data->result_array();
$arr_info_data->next_result();
if($arr_info_rslt[0]){
foreach($arr_info_rslt as $value){
$prime_id = $value['prime_arr_info_id'];
$project = $value['project_id'];
$project_desc = $picklist_arr['project_id']['array_list'][$project];
$wbs = $value['wbs_element'];
$wbs_desc = $picklist_arr['wbs_element']['array_list'][$wbs];
$from_date = $value['arr_from_date'];
$to_date = $value['arr_to_date'];
$pos = $value['position'];
$rev_pos = $value['rev_position'];
$pay_pos = $value['pay_struct_position'];
$pf_exclude = $value['pf_exclude'];
$pf_exc_desc = $status_arr[$pf_exclude];
$esi_exclude = $value['esi_exclude'];
$esi_exc_desc = $status_arr[$esi_exclude];
$ot_exclude = $value['ot_exclude'];
$ot_exc_desc = $status_arr[$ot_exclude];
$arr_status = (int)$value['arr_status'];
// $entry_status = (int)$value['entry_status'];
$entry_status = (int)$entry_array[$value['prime_arr_info_id']]['entry_status'];
$created_date = $value['trans_created_date'];
# Gross.
$pos_gross = $value['pos_gross'];
$rev_gross = $value['rev_gross'];
$pay_gross = $value['pay_gross'];
# WBS.
$pos_wbs = $value['pos_wbs'];
$rev_wbs = $value['rev_wbs'];
$pay_wbs = $value['pay_wbs'];
# Position.
$pos_desc = $picklist_arr['position']['array_list'][$pos];
$position = $pos_wbs.' ~ '.$pos_gross.' ~ '.$pos_desc;
# Rev Position.
$rev_pos_desc = $picklist_arr['position']['array_list'][$rev_pos];
if($rev_wbs && $rev_gross && $rev_pos_desc){
$rev_position = $rev_wbs.' ~ '.$rev_pos_desc;
}else{
$rev_position = 'Transfer';
}
# Pay Position.
$pay_pos_desc = $picklist_arr['position']['array_list'][$pay_pos];
$pay_position = $pay_wbs.' ~ '.$pay_gross.' ~ '.$pay_pos_desc;
$status_td = '';
if($rev_pos === 'transfer'){
$rev_pos_desc = 'Transfer';
}
# DELETE OPTION BASED ON USERROLE!
if($arr_status === 1){
$tr_line .= "<tr><td><input type='hidden' id='prime_arr_id' name='prime_arr_id' class='prime_arr_id' value='$prime_id'></td>";
$status_td = "<td><input type='checkbox' id='arr_status' name='arr_status' class='arr_status' checked readonly></td>";
$status_td = "<td style = 'color:green;'>Completed</td>";
}elseif($arr_status === 3){
$tr_line .= "<tr><td><input type='checkbox' id='prime_arr_id' name='prime_arr_id' class='prime_arr_id' value='$prime_id'></td>";
$status_td = "<td><input type='checkbox' id='arr_status' name='arr_status' class='arr_status' readonly></td>";
$status_td = "<td style ='color:red;'>Rejected</td>";
}else{
if($entry_status === 1){
$tr_line .= "<tr><td><input type='hidden' id='prime_arr_id' name='prime_arr_id' class='prime_arr_id' value='$prime_id'></td>";
}else{
$tr_line .= "<tr><td><input type='checkbox' id='prime_arr_id' name='prime_arr_id' class='prime_arr_id' value='$prime_id'></td>";
}
$status_td = "<td><input type='checkbox' id='arr_status' name='arr_status' class='arr_status' readonly></td>";
$status_td = "<td style ='color:orange;'>Pending</td>";
}
$tr_line .= "<td>$project_desc</td><td>$wbs_desc</td><td>$from_date</td><td>$to_date</td><td>$position</td><td>$rev_position</td><td>$pay_position</td><td>$pf_exc_desc</td><td>$esi_exc_desc</td><td>$ot_exc_desc</td>$status_td</tr>";
}
}
$sts = 1;
$response = "Arrear Details in Below Table..!";
if(!$tr_line){
$sts = 2;
$response = "No Data Available..!";
$tr_line = "<tr><td></td><td></td><td></td>No Data Available<td></td><td></td><td></td><td></td></tr>";
}
$table_info = "<thead><tr>
<th class = select-checkbox><input type='checkbox' name='select_all' class='select_all'></th><th>Project</th><th>Wbs Element</th><th>Start Date</th><th>End Date</th><th>Position</th><th>Rev Position</th><th>Pay Position</th><th>PF Exclude</th><th>ESI Exclude</th><th>OT Exclude</th><th>Arrear Status</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>";
if($sts === 1){
echo json_encode(array('success' => TRUE, 'message' => $response,'table_info'=>$table_info));
}else{
echo json_encode(array('success' => FALSE, 'message' => $response,'table_info'=>$table_info));
}
}
}
//FUNCTION FOR GET A ARREAR INFO TABLE DATA 11OCT22 END
//PICKLSIT DATA DESCRIPTION GET FUNCTION USING FORM SETTING 11OCT22 START
public function picklist_data_function(){
$form_info_qry = 'select prime_form_id,prime_module_id,input_view_type,input_for,field_type,label_name,view_name,field_length,field_decimals,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value,field_isdefault,file_type,mandatory_field,unique_field,table_show,search_show,default_value,pick_display_value,pick_list_import from cw_form_setting where prime_module_id = "employees" and label_name in ("project_id","wbs_element","position") and cw_form_setting.trans_status = 1';
$form_info_data = $this->db->query("CALL sp_a_run ('SELECT','$form_info_qry')");
$form_info_rslt = $form_info_data->result();
$form_info_data->next_result();
$pick_list_arr = [];
if($form_info_rslt[0]){
$pick_list_arr = $this->get_pick_list_data($form_info_rslt);
}
return $pick_list_arr;
}
public function get_pick_list_data($form_info_rslt){
$pro_qry = [];
$pick_list_arr = [];
foreach($form_info_rslt as $setting){
$prime_form_id = (int)$setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_view_type = (int)$setting->input_view_type;
$input_for = (int)$setting->input_for;
$field_type = (int)$setting->field_type;
$label_id = $setting->label_name;
$label_name = ucwords($setting->view_name);
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list_type = (int)$setting->pick_list_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$field_isdefault = (int)$setting->field_isdefault;
$file_type = (int)$setting->file_type;
$mandatory_field = (int)$setting->mandatory_field;
$unique_field = (int)$setting->unique_field;
$table_show = (int)$setting->table_show;
$search_show = (int)$setting->search_show;
$default_value = (int)$setting->default_value;
$pick_display = $setting->pick_display_value;
$pick_list_import = (int)$setting->pick_list_import;
if($default_value === 0){
$default_value = "";
}
$pick_drop = array();
$pick_master = array();
$pick_key = array();
$pick_val = array();
$final_pick = array();
$where_condition = "";
if($field_isdefault === 1){ // UDY NEED CHECK
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];
if($pick_display){
$pick_list = "$pick_list_val_1,CONCAT_WS(\" ~ \", $pick_display) as $pick_list_val_2";
// $pick_qry = 'select '.$pick_list.' from '.$pick_table.' where trans_status = 1 '.$where_condition;
if($pick_table === "cw_sap_wbs"){
$pick_qry = 'select '.$pick_list.' from '.$pick_table.' inner join cw_pay_structure on cw_pay_structure.wbs_element = '.$pick_table.'.'.$pick_list_val_1.' where '.$pick_table.'.trans_status = 1 and cw_pay_structure.personal_code in ('.rtrim($this->logged_area_access,',').') '.$where_condition;
}
if($pick_table === "cw_sap_position"){
$pick_qry = 'select '.$pick_list.' from '.$pick_table.' inner join cw_pay_structure on cw_pay_structure.position = '.$pick_table.'.'.$pick_list_val_1.' where '.$pick_table.'.trans_status = 1 and cw_pay_structure.personal_code in ('.rtrim($this->logged_area_access,',').') '.$where_condition;
}else{
$pick_qry = 'select '.$pick_list.' from '.$pick_table.' where trans_status = 1 '.$where_condition;
}
$pick_qry = rtrim($pick_qry," ");
$pro_qry[] = array("return"=>$label_id,"qry"=>$pick_qry,"pro_info"=>array('prime_form_id' => $prime_form_id,'label_id' => $label_id,'label_name' => $label_name,'field_type' => $field_type,'pick_list_type' => $pick_list_type,'pick_list_val_1' => $pick_list_val_1,'pick_list_val_2' => $pick_list_val_2,'table_show' => $table_show,'search_show' => $search_show,'field_isdefault' => $field_isdefault,));
}
}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_qry = 'select '.$pick_list_val_1.','.$pick_list_val_2.' from '.$pick_table.' where '.$pick_list_val_3.' = 1'. $where_condition;
$pick_qry = rtrim($pick_qry," ");
$pro_qry[] = array("return"=>$label_id,"qry"=>$pick_qry,"pro_info"=>array('prime_form_id' => $prime_form_id,'label_id' => $label_id,'label_name' => $label_name,'field_type' => $field_type,'pick_list_type' => $pick_list_type,'pick_list_val_1' => $pick_list_val_1,'pick_list_val_2' => $pick_list_val_2,'table_show' => $table_show,'search_show' => $search_show,'field_isdefault' => $field_isdefault,));
}
}else
if($field_type === 9){
if($pick_table === "cw_sap_wbs"){
$pick_display_val = "CONCAT(".str_replace(",",'," ~ ",',$pick_display).") as auto_list_pick";
$pick_qry = 'select '.$auto_prime_id.','.$pick_display_val .' from '. $pick_table .' inner join cw_pay_structure on cw_pay_structure.wbs_element = '.$pick_table.'.'.$auto_prime_id.' where '.$pick_table.'.trans_status = 1 and cw_pay_structure.personal_code in ('.rtrim($this->logged_area_access,',').') ';
}else{
$pick_display_val = "CONCAT(".str_replace(",",'," ~ ",',$pick_display).") as auto_list_pick";
$pick_qry = 'select '.$auto_prime_id.','.$auto_dispaly_value.','.$pick_display_val .' from '. $pick_table .' where trans_status = 1';
}
$pick_qry = rtrim($pick_qry," ");
$pro_qry[] = array("return"=>$label_id,"qry"=>$pick_qry,"pro_info"=>array('prime_form_id' => $prime_form_id,'label_id' => $label_id,'label_name' => $label_name,'field_type' => $field_type,'auto_prime_id' => $auto_prime_id,'auto_dispaly_value' => $auto_dispaly_value,'table_show' => $table_show,'search_show' => $search_show,'field_isdefault' => $field_isdefault,));
}
}
}
if(count($pro_qry ?? []) > 0){
$pick_list_info = $this->run_multi_qry($pro_qry);
$pick_list_rslt = [];
$pick_list_rslt = json_decode(json_encode($pick_list_info->rslt),true);
foreach($pick_list_rslt as $pick_key=>$list_info){
$is_exist_pro_qry = array_search($pick_key, array_column($pro_qry ?? [], 'return') ?? []);
if($pro_qry[$is_exist_pro_qry]){
//pro info array format
$pro_info = $pro_qry[$is_exist_pro_qry]["pro_info"];
$prime_form_id = $pro_info['prime_form_id'];
$label_id = $pro_info['label_id'];
$label_name = $pro_info['label_name'];
$field_type = (int)$pro_info['field_type'];
$table_show = (int)$pro_info['table_show'];
$search_show = (int)$pro_info['search_show'];
$field_isdefault = (int)$pro_info['field_isdefault'];
if($field_type === 5 || $field_type === 7){
$pick_list_type = (int)$pro_info['pick_list_type'];
$pick_list_val_1 = $pro_info['pick_list_val_1'];
$pick_list_val_2 = $pro_info['pick_list_val_2'];
// if($pick_list_type === 1){
$pick_key = array_column($list_info ?? [], $pick_list_val_1);
$pick_val = array_column($list_info ?? [], $pick_list_val_2);
$final_pick = array_combine( $pick_key ?? [], $pick_val ?? []);
if($final_pick){
$final_pick = array("" => "---- $label_name ----") + $final_pick;
}
// }
}else
if($field_type === 9){
$auto_prime_id = $pro_info['auto_prime_id'];
$auto_dispaly_value = $pro_info['auto_dispaly_value'];
$pick_key = array_column($list_info ?? [], $auto_prime_id);
$pick_val = array_column($list_info ?? [], 'auto_list_pick');
$final_pick = array_combine( $pick_key ?? [], $pick_val ?? []);
}
$pick_list_arr[$label_id] = array('label_id'=> $label_id, 'label_name'=> $label_name, 'field_isdefault'=> $field_isdefault, 'array_list'=> $final_pick, 'field_type'=> $field_type,'prime_form_id'=>$prime_form_id);
}
}
}
return $pick_list_arr;
}
//PICKLSIT DATA DESCRIPTION GET FUNCTION USING FORM SETTING 11OCT22 START
//ARREAR SEARCH ENTRY DELETE PROCESS 110CT22 START
public function arr_info_delete(){
$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);
}
$delete_ids = implode(',',$this->input->post('delete_ids') ?? []);
$created_on = date("Y-m-d H:i:s");
if($delete_ids){
$upd_delete_query = 'UPDATE cw_arr_info SET trans_status = 0,trans_deleted_by = "'. $this->logged_id .'",trans_deleted_date = "'.$created_on.'" WHERE cw_arr_info.prime_arr_info_id in ('. $delete_ids .') and cw_arr_info.trans_status = 1';
if($this->db->query("CALL sp_a_run ('UPDATE','$upd_delete_query')")){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to delete"));
}
}
}
#Filtered project based on from and to date.
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);
}
$from_month = $this->db->escape(date("Y-m-01",strtotime("01-" . $this->input->post('from_month'))));
$to_month = $this->db->escape(date("Y-m-01",strtotime("01-" . $this->input->post('to_month'))));
$logged_area = rtrim($this->logged_area_access,",");
$pro_id_data = $this->db->query("CALL sp_a_run ('SELECT','select prime_sap_project_id,pro_id,pro_desc from cw_sap_project JOIN cw_transactions_fms ON cw_sap_project.pro_id = cw_transactions_fms.project_id where cw_sap_project.trans_status = 1 and cw_sap_project.pro_personal_area_id in ($logged_area)AND DATE_FORMAT(STR_TO_DATE(CONCAT(\"01-\",cw_transactions_fms.transactions_month),\"%d-%m-%Y\"),\"%Y-%m-%d\") BETWEEN '$from_month' AND '$to_month' GROUP BY pro_id ')");
$pro_id_rslt = $pro_id_data->result_array();
$pro_id_data->next_result();
$pro_id_list = "<option value = ''>---- Select Project ----</option>";
$pro_id_desc = array();
foreach($pro_id_rslt as $for){
$prime_id = $for['prime_sap_project_id'];
$pro_id = $for['pro_id'];
$pro_desc = $for['pro_desc'];
$pro_id_list .= "<option data-value='$pro_id' value='$pro_id'> $pro_desc </option>";
}
echo json_encode(array('success'=>TRUE,'pro_id_list'=>$pro_id_list));
}
#Filtered month based on from and to date,project.
public function get_month(){
$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);
}
$project_id = $this->input->post('project_id');
$from_wbs = $this->input->post('from_wbs');
$to_wbs = $this->input->post('to_wbs');
$get_wbs_qry = 'SELECT prime_arr_info_id,arr_from_date,arr_to_date,cw_arr_info.wbs_element FROM cw_arr_info INNER JOIN cw_sap_wbs ON cw_sap_wbs.wbs_id = cw_arr_info.wbs_element WHERE cw_arr_info.trans_status = 1 AND cw_sap_wbs.trans_status = 1 AND cw_arr_info.project_id = "'.$project_id.'" AND (cw_arr_info.wbs_element = "'.$from_wbs.'" OR cw_arr_info.wbs_element = "'.$to_wbs.'") ';
$get_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$get_wbs_qry')");
$get_wbs_rslt = $get_wbs_info->result_array();
$get_wbs_info->next_result();
foreach($get_wbs_rslt as &$dateRange){
$fromDate = DateTime::createFromFormat('m-Y', $dateRange['arr_from_date']);
$toDate = DateTime::createFromFormat('m-Y', $dateRange['arr_to_date']);
$dateRange['arr_from_date'] = $fromDate->format('M-Y');
$dateRange['arr_to_date'] = $toDate->format('M-Y');
}
if($get_wbs_rslt){
echo json_encode(array("success"=>TRUE,"wbs"=>$get_wbs_rslt));
}else{
echo json_encode(array("success"=>FALSE,"message"=>"No Records Found.!"));
}
}
#Filtered position based on wbs,from and to date,project.
public function get_position_newentry(){
$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);
}
$project_id = $this->input->post('project_id');
$from_wbs = $this->input->post('from_wbs');
$to_wbs = $this->input->post('to_wbs');
$month = $this->input->post('month');
$split_month = explode(" ", $month ?? "");
$from_month = DateTime::createFromFormat('M-Y', $split_month[0])->format('m-Y');
$to_month = DateTime::createFromFormat('M-Y', $split_month[2])->format('m-Y');
$get_pos_qry = 'SELECT cw_sap_position.position_code,cw_sap_position.position_name,cw_sap_position.prime_sap_position_id FROM cw_arr_info INNER JOIN cw_sap_position ON cw_sap_position.position_code = cw_arr_info.position WHERE cw_arr_info.trans_status = 1 and cw_sap_position.trans_status = 1 and project_id="'.$project_id.'" and cw_arr_info.wbs_element <= "'.$from_wbs.'" and cw_arr_info.wbs_element >= "'.$to_wbs.'" and arr_from_date >= "'.$from_month.'" and arr_to_date <= "'.$to_month.'" group by cw_arr_info.position';
$get_pos_info = $this->db->query("CALL sp_a_run ('SELECT','$get_pos_qry')");
$get_pos_rslt = $get_pos_info->result_array();
$get_pos_info->next_result();
$pos_list = "";
foreach($get_pos_rslt as $for){
$prime_id = $for['prime_sap_position_id'];
$pos_id = $for['position_code'];
$pos_desc = $for['position_name'];
$pos_list .= "<option value='$pos_id'> $pos_id ~ $pos_desc </option>";
}
echo json_encode(array("success"=>true,"position_list"=>$pos_list));
}
public function convertToMonthYear($date){
$dateParts = explode('-', $date ?? "");
$month = date_parse($dateParts[0])['month'];
$year = $dateParts[1];
return sprintf("%02d-%s", $month, $year);
}
}
?>