File: /home/cafsindia/uds.cafsinfotech.in/application/controllers/Process_payroll_man.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
require('./application/libraries/PHPSpreadsheet/autoload.php');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Process_payroll_man extends Action_controller{
public function __construct(){
parent::__construct('process_payroll_man');
}
// 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;
//FOR EXCEL IMPORT START 8OCT22
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "transactions_fms_man" 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;
//DR CODE FOR GET A PAYMENT TYPE
$pay_type_qry = 'select prime_manual_pay_type_id,pay_type from cw_manual_pay_type where cw_manual_pay_type.trans_status = 1';
$pay_type_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_type_qry')");
$pay_type_rslt = $pay_type_info->result();
$pay_type_info->next_result();
$pay_type_arr[""] = "---- Select Type ----";
foreach($pay_type_rslt as $for){
$prime_id = $for->prime_manual_pay_type_id;
$pay_type = $for->pay_type;
$pay_type_arr[$prime_id] = $pay_type;
}
$data['pay_type_list'] = $pay_type_arr;
$data['module_id'] = "transactions_fms_man";
//FOR EXCEL IMPORT END 8OCT22
//PERSONAL AREA FETCH
$per_qry = 'select prime_sap_personal_area_id,personal_code,personal_name from cw_sap_personal_area where trans_status = 1 and personal_code in ('.rtrim($this->logged_area_access,",").')';
$per_data = $this->db->query("CALL sp_a_run ('SELECT','$per_qry')");
$per_rslt = $per_data->result_array();
$per_data->next_result();
$per_id[''] = "---- Select Project----";
foreach($per_rslt as $for){
$prime_id = $for['prime_sap_personal_area_id'];
$pro_id = $for['personal_code'];
$pro_desc = $for['personal_name'];
$per_id[$pro_id] = $pro_id.' ~ '.$pro_desc;
}
$data['personal_code'] = $per_id;
$this->load->view("$this->control_name/manage",$data);
}
public function get_page_info(){
$this->page_info();
echo json_encode(array('success' => TRUE,'pick_list' => $this->pick_list));
}
//LOAD TABEL WITH FILTERS
//LOAD TABEL WITH FILTERS
public function search(){
$dec_data = $this->cryptoDecrypt($_POST['Payload']);
$_POST = $dec_data['data'];
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..'));
exit(0);
}
//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){
$data['primeId'] = $form_view_id;
if($form_view_id !== -1){
//Decrypt prime id from URL
$decRslt = $this->cryptoDecrypt(base64_decode(urldecode($form_view_id)));
$form_view_id = $decRslt['prime_id'];
if(!$form_view_id){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
}
//VIEW INFO FUNCTION
$this->view_info($form_view_id);
$data['encKey'] = $this->generateKey();
$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();
$this->load->view("$this->control_name/form",$data);
}
//SAVE MODEL DATA TO DATA BASE
public function save(){
//Encryption
$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);
}
//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);
}
}
//Sanitize SQL InJection chars
$value = $this->sanitize_input($value, $field_type);
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(){
//Encryption
$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') ?? []);
$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;
foreach($prime_module_id as $check_modules){
$table_name = "cw_".$check_modules;
$select_table = "$table_name.$label_name[$i]";
$select_where = " and $table_name.$label_name[$i] in ($delete_ids)";
$check_module_query = 'SELECT '.$select_table.' from '.$table_name.' WHERE '.$table_name.'.trans_status = 1 '.$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){
$table_names .= "$check_modules,";
$can_process = False;
$delete_status = False;
}
$i++;
}
}
$table_names = rtrim($table_names,",");
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(){
//Encryption
$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);
}
$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"));
}
}
//excel maaping format
public function man_excel($Payload){
//Decryption
$_POST = $this->cryptoDecrypt(base64_decode(urldecode($Payload)));
$module_id = $this->input->post('module_id');
$excel_format = $this->input->post('excel_format');
$excel_format_qry = 'select view_name,excel_line_column_name,excel_line_value,cw_form_setting.gross_check,cw_form_setting.deduction_check from cw_util_excel_format_line inner join cw_form_setting on cw_form_setting.label_name = excel_line_column_name where excel_line_module_id = "'.$module_id.'" and cw_form_setting.prime_module_id = "employees" and prime_excel_format_id ="'.$excel_format.'" and cw_util_excel_format_line.trans_status = 1 GROUP BY cw_form_setting.label_name';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$green = array(
'font' => array(
'bold' => true,
'color' => array('rgb' => '4bbf2a')
));
$red = array(
'font' => array(
'bold' => true,
'color' => array('rgb' => 'c04040')
));
////
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
# Set the first row as the header row
foreach($excel_result as $excel){
$excel_line_column_name = $excel->view_name;
$excel_line_value = $excel->excel_line_value;
$worksheet->getCell($excel_line_value."1")->setValue($excel_line_column_name);
if($excel->gross_check === "1"){
$worksheet->getStyle($excel_line_value."1", $excel_line_column_name)->applyFromArray($green);
}else
if($excel->deduction_check === "1"){
$worksheet->getStyle($excel_line_value."1", $excel_line_column_name)->applyFromArray($red);
}
}
$filename= $module_id.".xls";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename= "'.$filename.'"');
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');
echo json_encode(array('success' => TRUE, 'output' => $worksheet));
exit(0);
}
//SAVE IMPORT FILE PATH
public function save_import(){
$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_id = $this->control_name;
$process_month = $this->input->post('process_month');
$project_id = $this->input->post('project_id');
$personal_code = $this->input->post('personal_code');
$pay_type = $this->input->post('pay_type');
$excel_format = $this->input->post('excel_format');
$excel_file_path = $this->input->post('excel_file_path');
$excel_sheet_name = $this->input->post('excel_sheet_name');
$excel_start_row = $this->input->post('excel_start_row');
$excel_end_row = $this->input->post('excel_end_row');
$process_type = (int)$this->input->post('process_type');
$logged_id = $this->session->userdata('logged_id');
if($project_id){
$pro_qry = ' and project_id = "'.$project_id .'" ';
}
$today_date = date("Y-m-d h:i:s");
$import_query = 'insert into cw_month_import (module_id,excel_format,excel_file_path,excel_sheet_name,excel_start_row,excel_end_row,process_month,trans_created_by,trans_created_date) value ("'.$module_id.'","'.$excel_format.'","'.$excel_file_path.'","'.$excel_sheet_name.'","'.$excel_start_row.'","'.$excel_end_row.'","'.$process_month.'","'.$logged_id.'","'.$today_date.'")';
$import_info = $this->db->query("CALL sp_a_run ('INSERT','$import_query')");
$import_result = $import_info->result();
$import_info->next_result();
$import_id = $import_result[0]->ins_id;
//PROCESS TYPE 2 IS DELETE
if($process_type === 2){
$trans_tab_qry = 'select * from cw_transactions_fms_man where process_month = "'.$process_month.'" and man_pay_type = "'.$pay_type.'" and audit_status="2" '.$pro_qry.' and personal_code = "'.$personal_code.'" and trans_status = 1';
$trans_tab_info = $this->db->query("CALL sp_a_run ('select','$trans_tab_qry')");
$trans_tab_rslt = $trans_tab_info->result_array();
$trans_tab_info->next_result();
if(!$trans_tab_rslt){
echo json_encode(array('success' => false, 'message' => "No Records Found"));
exit(0);
}
echo json_encode(array('success' => true,'message' => 'Records to delete.!',"table_data"=>$trans_tab_rslt,"process_month"=>$process_month));
}else{
echo $this->do_excel_payroll_import($import_id,$pay_type);
}
}
//IMPORT DATA FROM FILE PATH
public function do_excel_payroll_import($import_id,$pay_type){
$created_on = date("Y-m-d H:i:s");
if($import_id < 0){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}
$excel_path_qry = 'select * from cw_month_import where import_id = "'.$import_id.'"';
$excel_path_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_path_qry')");
$excel_path_result = $excel_path_info->result();
$excel_path_info->next_result();
if(!$excel_path_result){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}else{
$this->prime_table = "cw_transactions_fms_man";
$excel_file_path = $excel_path_result[0]->excel_file_path;
$module_id = "transactions_fms_man";
$excel_format = $excel_path_result[0]->excel_format;
$excel_sheet_name = (int)$excel_path_result[0]->excel_sheet_name;
$excel_row_start = (int)$excel_path_result[0]->excel_start_row;
$excel_row_end = (int)$excel_path_result[0]->excel_end_row;
$process_month = $excel_path_result[0]->process_month;
$start_date = date("Y-m-t",strtotime('01-'.$process_month));
$end_date = date("Y-m-d",strtotime($start_date));
$emp_qry = 'SELECT cw_pay_structure.wbs_element,cw_pay_structure.position,cw_pay_structure.status,cw_sap_activity.act_no AS activity_no,cw_pay_structure.category,cw_pay_structure.tax_location FROM cw_pay_structure INNER JOIN cw_sap_activity ON cw_sap_activity.prime_sap_activity_id = cw_pay_structure.activity_no WHERE cw_pay_structure.status = 1';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_rslt = $emp_info->result_array();
$emp_info->next_result();
$emp_fil_rslt = array();
foreach($emp_rslt as $arr){
$emp_fil_rslt[$arr['wbs_element']][$arr['position']][$arr['activity_no']][$arr['category']] = $arr['status'];
}
$proj_wbs_chk_qry = 'SELECT wbs_id, wbs_project_id, wbs_personal_area_id FROM cw_sap_wbs WHERE wbs_start_date <= "'.$start_date.'" AND wbs_end_date >= "'.$end_date.'"';
$proj_chk_info = $this->db->query("CALL sp_a_run ('SELECT','$proj_wbs_chk_qry')");
$proj_chk_rslt = $proj_chk_info->result_array();
$proj_chk_info->next_result();
$proj_wbs_chk_arr = array();
foreach($proj_chk_rslt as $arr){
$proj_wbs_chk_arr[$arr['wbs_personal_area_id']][$arr['wbs_project_id']][$arr['wbs_id']]=$arr;
}
$format_qry = 'select * from cw_util_excel_format where prime_excel_format_id = "'.$excel_format.'" and cw_util_excel_format.trans_status = 1 and cw_util_excel_format.pay_type = "'.$pay_type.'"';
$format_info = $this->db->query("CALL sp_a_run ('SELECT','$format_qry')");
$format_rslt = $format_info->result();
$format_info->next_result();
if(!$format_rslt){
return json_encode(array('success' => false, 'message' => "Please add excel format before import"));
}else{
$exist_column_name = explode(",",$format_rslt[0]->exist_column_name);
$excel_format_qry = 'select unique_field,pick_list_import,view_name,label_name,field_type,pick_table,pick_list_type,pick_list,auto_prime_id,auto_dispaly_value,mandatory_field,field_isdefault,excel_line_column_name,excel_line_value from cw_util_excel_format_line inner join cw_form_setting on label_name = excel_line_column_name where excel_line_module_id = "'.$module_id.'" and prime_excel_format_id = "'.$excel_format.'" and cw_form_setting.prime_module_id = "employees" and cw_util_excel_format_line.trans_status = 1 group by excel_line_column_name ORDER BY excel_line_value ASC';
// order by prime_excel_format_line_id ASC
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_format_result = $excel_format->result();
$excel_format->next_result();
if(!$excel_format_result){
return json_encode(array('success' => false, 'message' => "Please map excel cell column before import"));
}else{
try{
$excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($excel_file_path);
}catch(Exception $e){
die('Error loading file "' . pathinfo($excel_file_path, PATHINFO_BASENAME). '": ' . $e->getMessage());
return json_encode(array('success' => false, 'message' => "Invalid file or path"));
}
$sheet = $excel_obj->getSheet($excel_sheet_name);
if($excel_row_end){
$total_rows = $excel_row_end;
}else{
$total_rows = $sheet->getHighestRow();
}
$highest_column = $sheet->getHighestColumn();
$error_info = array();
//PROCESS MONTH AND YEAR CONVERT INTO YEAR AND MONTH
$pro_year_mon = date("Y-m",strtotime("01-".$process_month));
//GET EMPLOYEE DETAILS ARRAY
$emp_qry = 'select prime_employees_id,employee_code,date_of_joining,date_of_birth,role,emp_name,termination_status,personal_code,project_id from cw_employees where cw_employees.trans_status = 1 and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m") <= "'.$pro_year_mon.'" and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, "%Y-%m") >= "'.$pro_year_mon.'"))';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_data_result = $emp_data->result();
$emp_data->next_result();
$emp_code_result = array_map(function($v)
{
$return_array['employee_data'] = $v;
$return_array['employee_code'] = $v->employee_code;
return $return_array;
}, $emp_data_result);
$emp_code_data = array_column($emp_code_result ?? [], 'employee_data', 'employee_code');
//ACTIVE PROJECT GET BASED ON PROCESS MONTH
$act_project_arr = $this->active_project_id($pro_year_mon);
//ACTIVE WBS GET BASED ON PROCESS MONTH
$act_wbs_arr = $this->active_wbs_id($pro_year_mon);
//ACTIVE ACTIVITY NO GET BASED ON PROCESS MONTH
$act_act_no_arr = $this->active_activity_id($pro_year_mon);
//QRY FOR GET A FORM SETTING BASED EMPLOYEE MASTER PAYROLL COLUMNS(INPUTS)
$form_qry = 'select prime_form_id,prime_module_id,label_name,view_name,field_type,pick_list_type,pick_list,pick_table,pick_display_value,pick_list_import,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "employees" and field_show = "1" and field_type in ("5","7","9") and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and label_name not in ("project_id","wbs_element","activity_no","network_id") ORDER BY payroll_sort asc';
// and (earn_payroll_check = "1" OR ded_payroll_check = "1")
$form_info = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_info_rslt = $form_info->result();
$form_info->next_result();
//FUNCTION FOR GET A FORM SETTING BASED EMPLOYEE MASTER PAYROLL COLUMNS(INPUTS) ARRAY
$all_pick_import_arr = $this->picklist_get_function("import",$form_info_rslt);
//DR QUERY FOR CHECK A EMPLOYEE MASTER FROM BASED EARNING AND DEDUCTION COLUMN GET START
$ear_ded_qry = 'select prime_form_id,prime_module_id,label_name,view_name,gross_check,deduction_check from cw_form_setting where prime_module_id = "employees" and trans_status = "1" and (gross_check = "1" OR deduction_check = "1")';
// and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for)
$ear_ded_info = $this->db->query("CALL sp_a_run ('SELECT','$ear_ded_qry')");
$ear_ded_info_rslt = $ear_ded_info->result_array();
$ear_ded_info->next_result();
//EARNING ARRAY
$ear_comp_arr = array_reduce($ear_ded_info_rslt ?? [], function($result, $arr){
if((int)$arr['gross_check'] === 1){
$result[$arr['label_name']] = $arr['label_name'];
}
return $result;
}, array());
//DEDUCTION ARRAY
$ded_comp_arr = array_reduce($ear_ded_info_rslt ?? [], function($result, $arr){
if((int)$arr['deduction_check'] === 1){
$result[$arr['label_name']] = $arr['label_name'];
}
return $result;
}, array());
//GET PAYROLL EXIST FUNCTION ARRAY START
// $payroll_exist_arr = $this->payroll_exist_arr($pro_year_mon);
//GET PAYROLL EXIST FUNCTION ARRAY END
//TABLE EXIST CHECK COLUMN ARRAY START
$exist_check_col = ['employee_code','project_id','personal_code','wbs_element','position','net_pay'];
//TABLE EXIST CHECK COLUMN ARRAY START
//QRY FOR GET A LAST TABLE ID(LAST ROW) FROM TRANSACTION FMS MANUAL TABLE START
$tab_last_id = $this->get_tab_last_id();
if(!$tab_last_id){
$tab_last_id = 1;
}
//QRY FOR GET A LAST TABLE ID(LAST ROW) FROM TRANSACTION FMS MANUAL TABLE END
for($row =$excel_row_start; $row <= $total_rows; $row++){
$prime_column_val = "";
$prime_cell_val = "";
$exist_val = "";
$status_info = array();
$pro_chk_arr = array();
$status_info["Excel Row"] = $row;
$sts = 1;
$total_earnings = 0.00;
$total_deductions = 0.00;
$net_pay = 0.00;
$tot_ear_val = 0.00;
$tot_ded_val = 0.00;
$net_val = 0.00;
$proj_id = "";
$wbs_element = "";
$per_code = "";
foreach($excel_format_result as $excel_info){
$field_isdefault = (int)$excel_info->field_isdefault;
$mandatory_field = (int)$excel_info->mandatory_field;
$field_type = (int)$excel_info->field_type;
$pick_table = $excel_info->pick_table;
$pick_list_type = (int)$excel_info->pick_list_type;
$pick_list = $excel_info->pick_list;
$auto_prime_id = $excel_info->auto_prime_id;
$auto_dispaly_value = $excel_info->auto_dispaly_value;
$excel_line_column_name = $excel_info->excel_line_column_name;
$excel_line_value = $excel_info->excel_line_value;
$get_cell_value = trim($sheet->getCell("$excel_line_value$row")->getCalculatedValue());
if($excel_line_column_name === "employee_code"){
$employee_code = $get_cell_value;
$emp_data = $emp_code_data[$get_cell_value];
$employees_id = $emp_data->prime_employees_id;
$emp_name = $emp_data->emp_name;
$emp_doj = $emp_data->date_of_joining;
$category = $emp_data->role;
$emp_status = (int)$emp_data->termination_status;
$personal_code = $emp_data->personal_code;
if(!array_key_exists($get_cell_value, $emp_code_data ?? [])){
$error_info['error']["$excel_line_value$row"] = "Employee Code Not Exist in DB";
}
}else
if($excel_line_column_name === 'position'){
$position = $get_cell_value;
$pro_chk_arr[$excel_line_column_name] = $excel_line_column_name;
}
else // FOR PICKLIST CHEC
if($field_type === 5 || $field_type === 7 || $field_type === 9){
if(!$get_cell_value){
$show_value = ucwords(str_replace("-"," ",$excel_line_column_name));
$error_info['error']["$excel_line_value$row"] = "$show_value Should Not Empty..!";
}else{
if($excel_line_column_name === "wbs_element"){
$pro_chk_arr[$excel_line_column_name] = $excel_line_column_name;
$wbs_element = $get_cell_value;
$get_key_val = $act_wbs_arr[$get_cell_value]['wbs_id'];
if (!$get_key_val) {
$error_info['error']["$excel_line_value$row"] = "Invalid ($get_cell_value) Data Mapped..! (or) please check Mapped Wbs Start date,Finish date...!";
}
}else
if($excel_line_column_name === "project_id"){
$pro_chk_arr[$excel_line_column_name] = $excel_line_column_name;
$proj_id = $get_cell_value;
$get_key_val = $act_project_arr[$get_cell_value];
if (!$get_key_val) {
$error_info['error']["$excel_line_value$row"] = "Invalid ($get_cell_value) Data Mapped..! or) please check Mapped Project Start date,Finish date...!";
}
}else
if($excel_line_column_name === "activity_no"){
$act_no = $get_cell_value;
$pro_chk_arr[$excel_line_column_name] = $excel_line_column_name;
if($wbs_element){
$get_key_val = $act_act_no_arr[$wbs_element][$get_cell_value]["act_no"];
if(!$get_key_val){
$error_info['error']["$excel_line_value$row"] = "Invalid ($get_cell_value) Data Mapped..!";
}
}else{
$error_info['error']["$excel_line_value$row"] = "WBS Element Invalid or WBS Element Should Map before Activity Column in Excel.!";
}
}
else {
if($excel_line_column_name === "role"){
$excel_line_column_name = "category";
$pro_chk_arr[$excel_line_column_name] = $excel_line_column_name;
$category = $get_cell_value;
}
$get_key_val = $all_pick_import_arr[$excel_line_column_name][$get_cell_value];
if(!$get_key_val){
if($excel_line_column_name === "category"){
$excel_line_column_name = "role";
$category = $get_cell_value;
$pro_chk_arr[$excel_line_column_name] = $excel_line_column_name;
}
$error_info['error']["$excel_line_value$row"] = "Unknown ($get_cell_value) Data Mapped..!";
}else{
//IN EMPLOYEE CURRENT PERSONAL AREA ONLY IMPORT
if($excel_line_column_name === "personal_code"){
$pro_chk_arr[$excel_line_column_name] = $excel_line_column_name;
$per_code = $get_cell_value;
if ($get_key_val !== $personal_code) {
$error_info['error']["$excel_line_value$row"] = "Invalid Personal Area ($get_cell_value) Mapped for Employee code ($employee_code) Please Check Employee Master.!";
}
}
}
}
$get_cell_value = $get_key_val;
}
}else
if($excel_line_column_name === 'total_earnings'){
$ear_col_row = $excel_line_value;
$total_earnings = number_format((int)$get_cell_value, 2, '.', '');
}else
if($excel_line_column_name === 'total_deductions'){
$ded_col_row = $excel_line_value;
$total_deductions = number_format((int)$get_cell_value, 2, '.', '');
}else
if($excel_line_column_name === 'net_pay'){
$net_col_row = $excel_line_value;
$net_pay = number_format((int)$get_cell_value, 2, '.', '');
}
//FORM EARNING CLOUMN BASED VALUE ADD
if(in_array($excel_line_column_name,$ear_comp_arr)){
$tot_ear_val = number_format((int)((int)$tot_ear_val + (float)$get_cell_value), 2, '.', '');
}
//FORM DEDUCTION CLOUMN BASED VALUE ADD
if(in_array($excel_line_column_name,$ded_comp_arr)){
$tot_ded_val = number_format((int)((int)$tot_ded_val + (float)$get_cell_value), 2, '.', '');
}
//Exist Check Condition from transaction fms manual table
if(in_array($excel_line_column_name,$exist_check_col)){
if($excel_line_column_name !== "process_month"){
$exist_val .= $excel_line_column_name.' = "'.$get_cell_value.'" and ';
}
}
}
//CHECK PROJECT PERSONAL CODE COMBINATION
if($wbs_element && $proj_id && $per_code){
if($per_code && $proj_id){
if(!$proj_wbs_chk_arr[$per_code][$proj_id]){
$error_info['error']["$row"] = "Invalid Personal code or Project Present";
}
}
//CHECK PROJECT AND WBS COMBINATION
if(!$proj_wbs_chk_arr[$per_code][$proj_id][$wbs_element]){
$error_info['error']["$row"] = "Invalid project or Wbs present";
}
}
//CHECK PAYSTRUCTURE AVAILABLE FOR THIS COMBINATION
if($wbs_element && $position && $act_no && $category){
if(!$emp_fil_rslt[$wbs_element][$position]){
$error_info['error']["$row"] = " Paystructure not Available for this WBS and Position...";
}else
if(!$emp_fil_rslt[$wbs_element][$position][$act_no][$category]){
$err_column_array['error']["$row"] = " Paystructure not Available for this Activity No and Category...";
}
}
//TOTAL EARNING CHECK
$net_pay_val = number_format((int)$tot_ear_val, 2, '.', '');
if($total_earnings !== $tot_ear_val){
$error_info['error']["$ear_col_row$row"] = "Total Earnings Value not Equal.!";
}
//TOTAL DEDUCTION CHECK
$net_pay_val = number_format((int)((int)$net_pay_val - $tot_ded_val), 2, '.', '');
if($total_deductions !== $tot_ded_val){
$error_info['error']["$ded_col_row$row"] = "Total Deductions Value not Equal.!";
}
//TOTAL NET PAY CHECK
if($net_pay !== $net_pay_val){
$error_info['error']["$net_col_row$row"] = "Total Netpay Value not Equal.!";
}
if($exist_val){
$exist_val = rtrim($exist_val, " and ");
//EXIST CHECK QUERY FROM TRANSACTION FMS MANUAL TABLE START 8OCT22
$exist_query = 'select count(*) exist_count from cw_transactions_fms_man where trans_status = 1 and '.$exist_val.' and process_month = "'.$process_month.'" and man_pay_type ="'.$pay_type.'"';
// and cw_transactions_fms_man.man_pay_type ="'.$pay_type.'"
$exist_info = $this->db->query("CALL sp_a_run ('RUN','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
$exist_count = (int)$exist_result[0]->exist_count;
if($exist_count > 0){
$error_info['error']["$row"] = "Tranasction Already Exist in DB.!";
}
//EXIST CHECK QUERY FROM TRANSACTION FMS MANUAL TABLE END 8OCT22
}
}
$can_process_count = count($error_info ?? []);
if ((int) $can_process_count > 0) {
$table_info = $this->get_excel_error_ui($error_info);
echo json_encode(array('success'=>false,'message'=>"Excel Error",'table_info'=>$table_info));
exit(0);
}else{
$prime_ins_val = "";
for($row = $excel_row_start; $row <= $total_rows; $row++){
$prime_column_val = "";
$prime_cell_val = "";
$exist_val = "";
$status_info = array();
$status_info["Excel Row"] = $row;
$sts = 1;
foreach($excel_format_result as $excel_info){
$field_isdefault = (int)$excel_info->field_isdefault;
$mandatory_field = (int)$excel_info->mandatory_field;
$field_type = (int)$excel_info->field_type;
$pick_table = $excel_info->pick_table;
$pick_list_type = (int)$excel_info->pick_list_type;
$pick_list = $excel_info->pick_list;
$auto_prime_id = $excel_info->auto_prime_id;
$auto_display_value = $excel_info->auto_display_value;
$excel_line_column_name = $excel_info->excel_line_column_name;
$excel_line_value = $excel_info->excel_line_value;
$get_cell_value = trim($sheet->getCell("$excel_line_value$row")->getCalculatedValue());
$get_key_val = "";
if($excel_line_column_name === "employee_code"){
$employee_code = $get_cell_value;
//Get Salary Start and End Date;
$emp_data = $emp_code_data[$get_cell_value];
$employees_id = $emp_data->prime_employees_id;
$emp_name = $emp_data->emp_name;
$emp_doj = $emp_data->date_of_joining;
$category = $emp_data->role;
$emp_status = (int)$emp_data->termination_status;
}else
if($field_type === 4){
$get_cell_value = trim(date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell("$excel_line_value$row")->getCalculatedValue())));
}else
// FOR PICKLIST CHECK
if($field_type === 5 || $field_type === 7 || $field_type === 9){
if($excel_line_column_name === "project_id"){
$get_key_val = $act_project_arr[$get_cell_value];
}else
if($excel_line_column_name === "wbs_element"){
$get_key_val = $act_wbs_arr[$get_cell_value]['wbs_id'];
$wbs_element = $get_key_val;
}else
if($excel_line_column_name === "activity_no"){
if($wbs_element){
$get_key_val = $act_act_no_arr[$wbs_element][$get_cell_value]['prime_sap_activity_id'];
}
}else{
if($excel_line_column_name === "role"){
$excel_line_column_name = "category";
}
$get_key_val = $all_pick_import_arr[$excel_line_column_name][$get_cell_value];
if($excel_line_column_name === "category"){
$excel_line_column_name = "role";
}
}
$get_cell_value = $get_key_val;
}
$prime_column_val .= $excel_line_column_name . ",";
$prime_cell_val .= '"' . $get_cell_value . '",';
}
$prime_ins_val .= $prime_cell_val.'"'.$pay_type.'","'.$employees_id.'","'.$emp_name.'","'.$process_month.'","'.$process_month.'","'.$emp_status.'","'.$this->logged_id.'",'.'"'.$created_on.'",'.'"'.$created_on.'"),(';
$status_info['Status'] = "Inserted to DB";
$status_array[] = $status_info;
}
if($prime_ins_val){
$prime_column_val .= "man_pay_type,employees_id,emp_name,transactions_month,process_month,termination_status,trans_created_by,sub_date,trans_created_date";
$prime_column_val = rtrim($prime_column_val,",");
$prime_ins_val = rtrim($prime_ins_val,"),(");
$prime_query = 'insert into cw_transactions_fms_man ('.$prime_column_val.') VALUES ('.$prime_ins_val.')';
$insert_info = $this->db->query($prime_query);
}
if($insert_info){
$table_info = $this->get_excel_import_ui($status_array);
echo json_encode(array('success'=>true,'message'=>"Successfully file imported..!",'table_info' => $table_info,'process_month' => $process_month));
}else{
echo json_encode(array('success'=>false,'message'=>"File not Imported.!"));
}
}
}
}
}
}
//ERROR COLUMN AND ROW DISPLAY FOR INVALID DATAS
public function get_excel_error_ui($err_column_tabview){
$table_info = "";
$th_line = "";
$tr_line = "";
foreach($err_column_tabview as $err_column){
foreach($err_column as $key => $value){
$tr_line .= "<tr><td>$key</td><td style='color:#ff0303 !important;'>$value</td></tr>";
}
}
$table_info = "<table class='table table-bordered'>
<thead>
<tr>
<th>Row and Column</th>
<th>Input Column</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $table_info;
}
//PAYROLL EXIST EMPLOYEES GET ARRAY STRAT 8OCT22
public function payroll_exist_arr($process_month){
$payroll_exist_arr = '';
$payroll_exist_qry = 'select role,employee_code,project_id,personal_code,wbs_element,position,activity_no from cw_transactions_fms_man where process_month ="'.$process_month.'"';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('RUN','$payroll_exist_qry')");
$payroll_exist_rslt = $payroll_exist_info->result_array();
$payroll_exist_info->next_result();
$payroll_exist_arr = array_reduce($payroll_exist_rslt ?? [], function($result, $arr){
$result[$arr['employee_code']][$arr['project_id']][$arr['personal_code']][$arr['wbs_element']][$arr['position']][$arr['activity_no']] = $arr['employee_code'];
return $result;
}, array());
return $payroll_exist_arr;
}
//PAYROLL EXIST EMPLOYEES GET ARRAY END 8OCT22
//DR FUNCTION FOR ACTIVE PROJECT GET 8OCT22
public function active_project_id($process_month){
$start_date = date("Y-m-t",strtotime($process_month.'-01'));
$end_date = date("Y-m-d",strtotime($process_month.'-01'));
$pro_id_arr = array();
$pro_id_qry = 'SELECT pro_id,pro_desc FROM `cw_sap_project` where cw_sap_project.pro_start_date <= "'.$start_date.'" and cw_sap_project.pro_end_date >= "'.$end_date.'" and cw_sap_project.trans_status = 1';
$pro_id_data = $this->db->query("CALL sp_a_run ('SELECT','$pro_id_qry')");
$pro_id_rslt = $pro_id_data->result_array();
$pro_id_data->next_result();
foreach($pro_id_rslt as $arr){
$pro_id_arr[$arr['pro_id']] = $arr['pro_id'];
}
return $pro_id_arr;
}
//DR FUNCTION FOR ACTIVE PROJECT GET 8OCT22
public function active_wbs_id($process_month){
$wbs_elem_arr = array();
$wbs_elem_qry = 'select prime_sap_wbs_id,wbs_id,wbs_desc,wbs_project_id from cw_sap_wbs where cw_sap_wbs.trans_status = 1 and date_format(wbs_start_date, "%Y-%m") <= "'.$process_month.'" and date_format(wbs_end_date, "%Y-%m") >= "'.$process_month.'"';
$wbs_elem_data = $this->db->query("CALL sp_a_run ('SELECT','$wbs_elem_qry')");
$wbs_elem_rslt = $wbs_elem_data->result_array();
$wbs_elem_data->next_result();
$wbs_elem_arr = array();
foreach($wbs_elem_rslt as $key => $value){
$wbs_elem_arr[$value["wbs_id"]]= $value;
}
return $wbs_elem_arr;
}
//DR FUNCTION FOR ACTIVE PROJECT GET 8OCT22
public function active_activity_id($process_month){
$activity_arr = array();
$activity_qry = 'select prime_sap_activity_id,act_no,act_desc,act_wbs_id from cw_sap_activity where cw_sap_activity.trans_status = 1 and date_format(act_start_date, "%Y-%m") <= "'.$process_month.'" and date_format(act_end_date, "%Y-%m") >= "'.$process_month.'" GROUP BY cw_sap_activity.prime_sap_activity_id,cw_sap_activity.act_wbs_id';
$activity_data = $this->db->query("CALL sp_a_run ('SELECT','$activity_qry')");
$activity_rslt = $activity_data->result_array();
$activity_data->next_result();
// $activity_arr = array_reduce($activity_rslt, function($result, $arr){
// $result[$arr['act_wbs_id']][$arr['act_no']] = $arr;
// return $result;
// }, array());
foreach($activity_rslt as $arr){
$activity_arr[$arr['act_wbs_id']][$arr['act_no']] = $arr;
}
return $activity_arr;
}
//FUNCTION FOR GET A TRNASACTION TABLE LAST ROW DATA 9OCT22 START
public function get_tab_last_id(){
$tab_last_id_qry = 'select prime_transactions_fms_man_id from cw_transactions_fms_man where cw_transactions_fms_man.trans_status = 1 ORDER BY prime_transactions_fms_man_id DESC LIMIT 1';
$tab_last_id_info = $this->db->query("CALL sp_a_run ('SELECT','$tab_last_id_qry')");
$tab_last_id_rslt = $tab_last_id_info->result();
$tab_last_id_info->next_result();
$tab_last_id = (int)$tab_last_id_rslt[0]->prime_transactions_fms_man_id;
return $tab_last_id;
}
//FUNCTION FOR GET A TRNASACTION TABLE LAST ROW DATA 9OCT22 END
//FUNCTION FOR GET A PROCESS MONTH BASED TRNASACTION TABLE DATA 9OCT22 START
public function get_trans_month_data($process_month,$pay_type){
$trans_tab_qry = 'select * from cw_transactions_fms_man where process_month = "'.$process_month.'" and man_pay_type = "'.$pay_type.'" and trans_status = 1';
$trans_tab_info = $this->db->query("CALL sp_a_run ('RUN','$trans_tab_qry')");
$trans_tab_rslt = $trans_tab_info->result_array();
$trans_tab_info->next_result();
return $trans_tab_rslt;
}
//FUNCTION FOR GET A PROCESS MONTH BASED TRNASACTION TABLE DATA 9OCT22 END
//FUNCTION FOR GET A SERACH MONTH BASED TRANSACTION DATA 9OCT22 START
public function search_trans_month_data(){
$process_month = $this->input->post('process_month');
$pay_type = $this->input->post('pay_type');
$table_data = $this->get_trans_month_data($process_month,$pay_type);
if($table_data[0]){
echo json_encode(array('success'=>true,'message'=>"Transaction Details",'table_data' => $table_data,'process_month' => $process_month));
}else{
echo json_encode(array('success'=>false,'message'=>"No Data Available",'table_data' => '','process_month' => $process_month));
}
}
//FUNCTION FOR GET A SERACH MONTH BASED TRANSACTION DATA 9OCT22 END
//PAY TYPE BASED EXCEL FORMAT GET
public function get_pay_type_excel_template(){
$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_id = $this->input->post('module_id');
$pay_type = $this->input->post('pay_type');
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "'.$module_id.'" and pay_type = "'.$pay_type.'" 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;
}
echo json_encode(array('success' => TRUE, 'excel_format_drop' => $excel_format_drop));
}
public function delete_data(){ //removed in manage.php
$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') ?? []).'"';
$process_month = $this->input->post('process_month');
$pay_type = $this->input->post('pay_type');
$personal_code = $this->input->post('personal_code');
$project_id = $this->input->post('project_id');
$logged_id = $this->session->userdata('logged_id');
if($project_id){
$pro_qry = 'and project_id ="'.$project_id.'"';
}
$trans_man_dup_qry = 'INSERT INTO cw_transactions_fms_man_dlt SELECT * FROM cw_transactions_fms_man WHERE cw_transactions_fms_man.trans_status = 1 and cw_transactions_fms_man.prime_transactions_fms_man_id in('.$delete_ids.')';
$save_result = $this->db->query("CALL sp_a_run ('RUN','$trans_man_dup_qry')");
if($save_result){
$trans_fms_man_dlt = 'DELETE from cw_transactions_fms_man WHERE trans_status = 1 and prime_transactions_fms_man_id in('.$delete_ids.')';
$this->db->query("CALL sp_a_run ('RUN','$trans_fms_man_dlt')");
$trans_man_upd_qry = 'UPDATE cw_transactions_fms_man_dlt SET trans_deleted_by = "' . $logged_id . '",trans_deleted_date = DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%S") WHERE trans_status = 1 and prime_transactions_fms_man_id in('.$delete_ids.')';
$this->db->query("CALL sp_a_run ('RUN','$trans_man_upd_qry')");
}
$trans_tab_qry = 'select * from cw_transactions_fms_man where process_month = "'.$process_month.'" and man_pay_type = "'.$pay_type.'" and audit_status="2" and personal_code ="'.$personal_code.'" '.$pro_qry.' and trans_status = 1';
$trans_tab_info = $this->db->query("CALL sp_a_run ('select','$trans_tab_qry')");
$trans_tab_rslt = $trans_tab_info->result_array();
$trans_tab_info->next_result();
echo json_encode(array('success' => true, 'message' => "Successfully Deleted...!","table_data"=>$trans_tab_rslt,"process_month"=>$process_month));
}
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');
//PROJECT ID GET BASED ON ARREAR INFO TABLE
$pro_id_qry = 'select prime_sap_project_id,pro_id,pro_desc from cw_sap_project where cw_sap_project.trans_status = 1 and cw_sap_project.pro_personal_area_id = "'.$personal_code.'"';
$pro_id_data = $this->db->query("CALL sp_a_run ('SELECT','$pro_id_qry')");
$pro_id_rslt = $pro_id_data->result_array();
$pro_id_data->next_result();
$pro_arr[''] = "---- Select Project----";
foreach($pro_id_rslt as $for){
$prime_id = $for['prime_sap_project_id'];
$pro_id = $for['pro_id'];
$pro_desc = $for['pro_desc'];
$pro_arr[$pro_id] = $pro_id.' ~ '.$pro_desc;
}
echo json_encode(array('project' => $pro_arr));
}
}
?>