File: /home/cafsindia/uds.cafsinfotech.in/application/controllers_bk/Monthly_input_fms.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Monthly_input_fms extends Action_controller{
public function __construct(){
parent::__construct('monthly_input_fms');
$this->load->model('Process_payroll_model');
if (!$this->Appconfig->isAppvalid()) {
redirect('config');
}
$this->prime_table = "cw_monthly_input_fms";
$lock_query = 'select GROUP_CONCAT(previous_column) as previous_column from cw_monthly_input_previous inner join cw_form_setting on cw_form_setting.label_name = previous_column where cw_monthly_input_previous.trans_status = 1 and column_status in (1)';
$lock_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_query')");
$lock_result = $lock_data->result();
$lock_data->next_result();
$previous_column = $lock_result[0]->previous_column;
$prev_qry = "";
if($previous_column){
$previous_column = '"'.str_replace(',', '","', $previous_column).'"';
$prev_qry = 'and label_name not in ('.$previous_column.')';
}
$table_query = 'select label_name,view_name,field_type from cw_form_setting where prime_module_id = "employees" and trans_status = "1" and (earn_month_check = "1" OR deduction_month_check = "1") and FIND_IN_SET("'.$this->logged_role.'",field_for) '.$prev_qry.' ORDER BY monthly_input_sort asc';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$result = $table_info->result();
$table_info->next_result();
$this->table_head = $result;
$select_key = array_column($result ?? [], "label_name");
$this->mi_select_query = "$this->prime_table.$this->prime_id,";
$this->mi_select_query .= "cw_monthly_input_fms.".implode(",cw_monthly_input_fms.",$select_key ?? []);
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$this->page_info();
//GET Monthly Input Columns
$data['table_head'] = $this->table_head;
$data['all_pick'] = $this->pick_list;
$data['fliter_list'] = $this->fliter_list;
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$category_list[""] = "---- Select Category ----";
foreach ($role_result as $for) {
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$lock_query = 'select previous_column,column_status from cw_monthly_input_previous inner join cw_form_setting on cw_form_setting.label_name = previous_column where cw_monthly_input_previous.trans_status = 1 and column_status in (1,2)';
$lock_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_query')");
$lock_result = $lock_data->result_array();
$lock_data->next_result();
foreach($lock_result as $arr){
$lock_result[$arr['previous_column']] = $arr['column_status'];
}
$data['lock_result'] = $lock_result;
//for filter settings get function
$data['mi_filter_setting'] = $this->mi_filter_col_setting();
//for filter purpose
$mi_filter_arr = $this->mi_filter_pick_column();
//for mi settings column arr
$data['mi_filter_pick_column'] = $mi_filter_arr;
$data['module_id'] = $this->control_name;
$data['encKey'] = $this->generateKey();
//FOR DEPENDENT DROPDOWN AND DEPENDENT PICKLIST BY LATHA
$this->load->view('monthly_input_fms/manage', $data);
}
public function mi_filter_pick_column(){
$mi_filter_column_rslt = $this->mi_filter_col_setting();
$process = "filter";
$mi_filter_arr = $this->pay_picklist_get_function($process,$mi_filter_column_rslt,"");
return $mi_filter_arr;
}
public function mi_import_pick_column(){
$mi_import_check_col_rslt = $this->mi_import_check_col_setting();
$process = "import";
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
public function search(){
$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->mi_select_query,$this->base_query);
$access_data = $this->session->userdata('access_data');
$tab_name = $this->input->post('tab_name');
$search_month = $this->input->post('search_month');
$search_mon_date = date("Y-m-d",strtotime("01-".$search_month));
$add_query = "";
$emp_qry = "";
$add_column = "";
$mi_filter_setting = $this->mi_filter_col_setting();
$label_name_arr = array();
foreach($mi_filter_setting as $setting){
$label_name = $setting->label_name;
$label_id = $this->input->post("$label_name");
$mandatory_col = (int)$setting->mandatory_column;
//This array only for using in monthly input fms table readonly option
$label_name_arr[$label_name] = $label_name;
if($label_id){
// if($mandatory_col === 1 || ($mandatory_col === 0 && $label_id !== "")){
$add_query .= ' and '.$this->prime_table.'.'.$label_name.' = "' . $label_id . '"';
$add_column .= ','.$label_name;
$emp_qry .=' and cw_employees.'.$label_name;
// }
}
}
$common_search = "";
if($search){
$common_search .= ' and (cw_monthly_input_fms.role like "'.$search.'%" or cw_monthly_input_fms.personal_code like "'.$search.'%" or cw_monthly_input_fms.project_id like "'.$search.'%" or cw_monthly_input_fms.wbs_element like "'.$search.'%" or cw_monthly_input_fms.position like "'.$search.'%" or cw_monthly_input_fms.activity_no like "'.$search.'%" or cw_monthly_input_fms.employee_code like "'.$search.'%" or cw_monthly_input_fms.emp_name like "'.$search.'%" or cw_monthly_input_fms.date_of_joining like "'.date('Y-m-d',strtotime($search)).'%")';
}
if($tab_name === 'input'){
$count_all_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_all_info = $this->db->query($count_all_query);
$count_all_rslt = $count_all_info->result();
$total_count = $count_all_rslt[0]->allcount;
$count_query = $count_all_query." where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" ".$common_search." $add_query and $this->prime_table.entry_status = 1 and $this->prime_table.check_status = 1 and $this->prime_table.payroll_status in (0,2) order by $this->prime_table.date_of_joining DESC";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " inner join cw_employees on cw_employees.employee_code = $this->prime_table.employee_code where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" ".$common_search." $add_query and $this->prime_table.entry_status = 1 and $this->prime_table.check_status = 1 and $this->prime_table.payroll_status in (0,2) order by $this->prime_table.date_of_joining DESC";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
if((int)$num_rows > 0){
if((int)$access_data['monthly_input']['access_delete'] === 1){
$btn_array = array();
foreach($this->table_head as $table){
$label_name = $table->label_name;
$view_name = $table->view_name;
$field_type = (int)$table->field_type;
if($label_name === "employee_code" || $label_name === "emp_name" || $label_name === "process_month" || $label_name === "date_of_joining" || $label_name === "date_of_birth" || $label_name_arr[$label_name] === $label_name || $field_type === 5 || $field_type === 9){
$btn_array[$label_name] = "";
}else{
$btn_array[$label_name] = "<a class='btn btn-xs btn-danger' id='delete_btn_".$label_name."' onclick=delete_column('".$label_name."','".$tab_name."')>delete</a>";
}
}
// array_unshift($search_result, $btn_array);
}
}
}else{
$count_all_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_all_info = $this->db->query($count_all_query);
$count_all_rslt = $count_all_info->result();
$total_count = $count_all_rslt[0]->allcount;
$count_query = $count_all_query." where $this->prime_table.trans_status = 1 and $this->prime_table.process_month =\"".$search_month."\" $add_query ".$common_search." and payroll_status = 1 order by $this->prime_table.date_of_joining DESC";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.process_month =\"".$search_month."\" $add_query ".$common_search." and payroll_status = 1 order by $this->prime_table.date_of_joining DESC";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
}
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
public function check_monthly_input(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$search_month = $this->input->post('search_month');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
//Check Lock Month
$exist_qry = 'select lock_month from cw_monthly_input_lock where lock_month = "'.$search_month.'" and status = 1 and trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$num_rows = $exist_data->num_rows();
$exist_data->next_result();
if ((int) $num_rows > 0) {
echo json_encode(array('success' => false,'status' => "locked",'message' => "Monthly Input Locked For this Month!!!"
));
exit(0);
}else{
if($search_month){
$mi_filter_setting_rslt = $this->mi_filter_col_setting();
if(!$mi_filter_setting_rslt[0]){
echo json_encode(array('success' => true,'message' => "Monthly Input Filter Setting not Mapped Please Map Filter Setting Columns..."));
}else{
$add_query = "";
foreach($mi_filter_setting_rslt as $setting){
$label_name = $setting->label_name;
$label_id = $this->input->post("$label_name");
if($label_id){
$add_query .= ' and '.$label_name.' = "' . $label_id . '"';
}
}
//Check monthy input data exist Start
// and cw_monthly_input_fms.termination_status = 0
$month_input_exist_qry = 'select employee_code from cw_monthly_input_fms where trans_status = 1 and process_month = "'.$search_month.'" and cw_monthly_input_fms.payroll = 1 '.$add_query.' limit 0,1 ';
$month_input_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$month_input_exist_qry')");
$month_input_exist_rslt = $month_input_exist_data->result();
$month_input_exist_data->next_result();
$month_input_exist_count = (int) $month_input_exist_data->num_rows();
if($month_input_exist_count === 0){
echo json_encode(array('success' => true,'message' => "Monthly Input Successfully Saved.","mi_check_arr" => $filter_col_arr));
}else{
echo json_encode(array('success' => true,'message' => "Monthly Input Already Exist for this Month."));
}
}
}
}
}
//DETELE COLUMN
public function delete_column(){
$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);
}
// $category = $this->input->post('category');
$search_month = $this->input->post('search_month');
$label_name = $this->input->post('label_name');
$created_on = date("Y-m-d H:i:s");
$prime_upd_query = '';
$mi_filter_check_col_rslt = $this->mi_filter_col_setting();
$add_mi_where_query = "";
foreach($mi_filter_check_col_rslt as $setting){
$label_column = $setting->label_name;
$label_id = $this->input->post("$label_name");
if($label_id){
$add_mi_where_query .= ' and '.$this->prime_table.'.'.$label_column.' = "' . $label_id . '"';
}
}
$upd_query = 'UPDATE '.$this->prime_table.' SET '.$label_name.' = 0,trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'" WHERE '.$this->prime_table.'.payroll_status in (0,2) and '.$this->prime_table.'.trans_status = 1 and '.$this->prime_table.'.process_month = "'.$search_month.'"'.$add_mi_where_query ;
if ($this->db->query("CALL sp_a_run ('RUN','$upd_query')")) {
echo json_encode(array(
'success' => true,
'message' => "Column Deleted successfully"
));
} else {
echo json_encode(array(
'success' => false,
'message' => "Unable to Delete, Please contact admin..!"
));
}
}
//CUSTOM DELETE IS UPDATED
public function delete_month_input(){
$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);
}
$month_ids = implode(",", $this->input->post('delete_ids') ?? []);
$search_month = $this->input->post('search_month');
//CHECK PAYROLL PROCESS OR NOT IF PROCESS PAYROLL WAS COMPLETED THEN NOT ALLOWED TO DELETE
$emp_count_qry = 'select count(prime_monthly_input_fms_id) as check_count from '.$this->prime_table.' where '.$this->prime_table.'.trans_status = 1 and '.$this->prime_table.'.payroll_status = 1 and '.$this->prime_table.'.prime_monthly_input_fms_id in ('.$month_ids.')';
$emp_count_info = $this->db->query("CALL sp_a_run ('RUN','$emp_count_qry')");
$emp_count_result = $emp_count_info->result();
$emp_count_info->next_result();
$check_count = (int)$emp_count_result[0]->check_count;
if ($check_count === 0){
$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 entry_status = 0,check_status = 0,'.$prime_upd_query.' WHERE '.$this->prime_id.' in ('.$month_ids.')';
$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' => "Payroll Already Exist Please delete the payroll for this month $search_month"
));
}
}
//check supplymentry proces 17 APR 2020
public function check_supplymentry(){
$search_month = $this->input->post('search_month');
$month_input_exist_qry = 'select employee_code from cw_monthly_input_fms where cw_monthly_input_fms.trans_status = 1 and cw_monthly_input_fms.process_month = "' . $search_month . '" limit 0,1';
$month_input_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$month_input_exist_qry')");
$month_input_exist_rslt = $month_input_exist_data->result();
$month_input_exist_data->next_result();
$month_input_exist_count = (int) $month_input_exist_data->num_rows();
if ($month_input_exist_count == 0) {
$supplymentry_ext_qry = 'select count(*) supply_count from cw_supplementary_detail where trans_status = 1 and supplementary_month ="' . $search_month . '" and category ="' . $category . '"';
$supplymentry_ext_info = $this->db->query("CALL sp_a_run ('RUN','$supplymentry_ext_qry')");
$supplymentry_ext_result = $supplymentry_ext_info->result();
$supplymentry_ext_info->next_result();
$supply_count = $supplymentry_ext_result[0]->supply_count;
if ((int) $supply_count === 0) {
echo json_encode(array(
'success' => TRUE,
'message' => "Supplementary Not Processed?"
));
} else {
echo json_encode(array(
'success' => FALSE,
'message' => "Ok Processed"
));
}
} else {
echo json_encode(array(
'success' => FALSE,
'message' => "Ok Processed"
));
}
}
public function update_supplymentry($role,$search_month){
$back_month_date = "01-".$search_month;
$back_month = date("m-Y", strtotime("-1 month", strtotime($back_month_date)));
$supplymentry_sts_qry = 'select count(*) as rslt_count from cw_monthly_input_fms where cw_monthly_input_fms.process_month="'.$back_month.'" and cw_monthly_input_fms.supplementary_status = 1 and cw_monthly_input_fms.trans_status = 1';
$supplymentry_sts_data = $this->db->query("CALL sp_a_run ('SELECT','$supplymentry_sts_qry')");
$supplymentry_sts_result = $supplymentry_sts_data->result();
$supplymentry_sts_data->next_result();
$supply_mi_count = $supplymentry_sts_result[0]->rslt_count;
$supplymentry_exit_qry = 'select count(*) as supply_count from cw_supplementary_detail where supplementary_month = "'.$search_month.'" and category = "'.$role.'" and trans_status = 1';
$supplymentry_data = $this->db->query("CALL sp_a_run ('SELECT','$supplymentry_exit_qry')");
$supplymentry_result = $supplymentry_data->result();
$supplymentry_data->next_result();
$supply_exit_count = $supplymentry_result[0]->supply_count;
if((int)$supply_mi_count > 0){
if((int)$supply_exit_count > 0){
$get_supplymentry_qry = 'select IFNULL(sum(supp_month_days),0) as supp_month_days,IFNULL(sum(supp_paid_days),0) as supp_paid_days,emp_code from cw_supplementary_detail inner join cw_monthly_input on cw_monthly_input.employee_code =cw_supplementary_detail.emp_code where cw_monthly_input.trans_status=1 and cw_supplementary_detail.supplementary_month="'.$search_month.'" and category = "'.$role.'" and supplementary_status = 1 group by emp_code';
$get_supplymentry_data = $this->db->query("CALL sp_a_run ('SELECT','$get_supplymentry_qry')");
$get_supplymentry_result = $get_supplymentry_data->result();
$get_supplymentry_data->next_result();
$update_on = date("Y-m-d H:i:s");
if(!empty($get_supplymentry_result)){
foreach($get_supplymentry_result as $sup_rslt){
$emp_code = $sup_rslt->emp_code;
$supp_paid_days = $sup_rslt->supp_paid_days;
$supp_month_days = $sup_rslt->supp_month_days;
$upd_mi_query = 'UPDATE cw_monthly_input SET supplementary_day ="'.$supp_paid_days.'", supplementary_month ="'.$supp_month_days.'", trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$update_on.'" WHERE employee_code = "'.$emp_code.'" and process_month="'.$search_month.'" and trans_status=1';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_mi_query')");
}
}
}
}
return true;
}
/* ==============================================================*/
/* ============ MONHTLY IMPORT OPERATION - START ================*/
/* ==============================================================*/
//IMPORT FILE VIEW INFORMATION
public function import(){
$data['module_id'] = $this->control_name;
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$category_list[""] = "---- Select Category ----";
foreach ($role_result as $for) {
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$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('monthly_input_fms/import', $data);
}
//NEHA EDIT START 06APR2020
public function month_fms_excel($module_id, $excel_format){
$excel_format_qry = 'select excel_name,view_name,excel_line_column_name,excel_line_value from cw_util_excel_format_line inner join cw_form_setting on cw_form_setting.label_name = excel_line_column_name inner join cw_util_excel_format on cw_util_excel_format.prime_excel_format_id = cw_util_excel_format_line.prime_excel_format_id where excel_line_module_id = "'.$module_id.'" and cw_util_excel_format_line.prime_excel_format_id ="'.$excel_format.'" and cw_util_excel_format_line.trans_status = 1 and cw_util_excel_format.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();
$excel_name = str_replace(' ', '_', $excel_result[0]->excel_name);
require_once APPPATH . "/third_party/PHPExcel.php";
$obj = new PHPExcel();
//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;
$obj->getActiveSheet()->setCellValue($excel_line_value . "1", $excel_line_column_name);
}
// Rename worksheet name
$filename= $excel_name.".xls"; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($obj, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
echo json_encode(array(
'success' => TRUE,
'output' => $excelOutput
));
}
//Sheet Name display in import page
public function sheet_name(){
$file_path = $this->input->post('file_path');
$filename = dirname(__FILE__) . "/php_excel/PHPExcel/IOFactory.php";
include($filename);
$excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($file_path);
$sheet_count = $excel_obj->getSheetCount();
$sheet_name = array();
for ($i = 0; $i < $sheet_count; $i++) {
$sheet = $excel_obj->getSheet($i);
$sheet_name[] = $sheet->getTitle();
}
echo json_encode(array(
'sheet_name' => $sheet_name
));
}
//SAVE MONTHLY IMPORT FILE PATH
public function save_import(){
$module_id = $this->input->post('module_id');
$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_month = $this->input->post('process_month');
$logged_id = $this->session->userdata('logged_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;
echo $this->do_monthly_excel_import($import_id);
}
//IMPORT DATA FROM FILE PATH
public function do_monthly_excel_import($import_id){
$created_on = date("Y-m-d H:i:s");
$filename = dirname(__FILE__) . "/php_excel/PHPExcel/IOFactory.php";
include($filename);
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 {
$excel_file_path = $excel_path_result[0]->excel_file_path;
$module_id = $excel_path_result[0]->module_id;
$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;
$qry_search_month = date("Y-m", strtotime("01-".$process_month));
$process_mon_date = date("Y-m-d",strtotime("01-".$process_month));
//employee check in employee master
$emp_qry = 'select employee_code,date_of_joining,date_of_birth,role,emp_name,prime_employees_id,termination_status,emp_cost_center_code from cw_employees where cw_employees.trans_status = 1 and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m") <= "'.$qry_search_month.'" and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, "%Y-%m") >= "'.$qry_search_month.'")) and cw_employees.entry_flag = "e"';
$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');
$lock_month_qry = 'select * from cw_monthly_input_lock where lock_month = "'.$process_month.'" and status = 1 and trans_status = 1';
$lock_month_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_month_qry')");
$lock_month_result = $lock_month_data->result();
$lock_month_data->next_result();
$lock_num_rows = $lock_month_data->num_rows();
if ((int) $lock_num_rows > 0) {
return json_encode(array(
'success' => false,
'message' => "Monthly input is locked this month, please unlocked to upload the files"
));
exit(0);
}
$format_qry = 'select * from cw_util_excel_format where prime_excel_format_id = "'.$excel_format.'" and cw_util_excel_format.trans_status = 1';
$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 * from cw_util_excel_format_line inner join cw_form_setting on label_name = excel_line_column_name where cw_form_setting.prime_module_id = "employees" and excel_line_module_id = "' . $module_id . '" and prime_excel_format_id = "'.$excel_format.'" and cw_util_excel_format_line.trans_status = 1 group by excel_line_column_name ORDER BY excel_line_value ASC';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_format_result = $excel_format->result();
$excel_format->next_result();
$excel_columns = array_column($excel_format_result ?? [], 'excel_line_column_name');
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"
));
}
//---------------------IMPORTANT CODE FOR MI IMPORT PROCESS---------------
//SAP ACTIVITY NUMBER GET CODE START BASED ON WBS
$sap_act_no_qry = 'select prime_sap_activity_id as prime_id,act_no,act_wbs_id from cw_sap_activity where cw_sap_activity.trans_status = 1 GROUP BY cw_sap_activity.prime_sap_activity_id,cw_sap_activity.act_wbs_id';
$sap_act_no_info = $this->db->query("CALL sp_a_run ('SELECT','$sap_act_no_qry')");
$sap_act_no_rslt = $sap_act_no_info->result_array();
$sap_act_no_info->next_result();
foreach($sap_act_no_rslt as $arr){
$sap_act_no_arr[$arr['act_wbs_id']][$arr['act_no']] = $arr['prime_id'];
}
//SAP ACTIVITY NUMBER GET CODE END BASED ON WBS
//GET PAY STRUCTURE DETAILS FOR DOT AND OT INPUT CHECK ARRAY
$pay_struct_qry = 'select cw_pay_structure.wbs_element,cw_pay_structure.wbs_element,cw_pay_structure.position,cw_pay_structure.activity_no,hrms_field_name,amount from cw_pay_structure inner join cw_pay_structure_line on cw_pay_structure.prime_pay_structure_id = cw_pay_structure_line.prime_pay_structure_id where cw_pay_structure.personal_code = "'.$personal_code.'" and cw_pay_structure.from_date <= "'.$process_mon_date.'" and cw_pay_structure.to_date >= "'.$process_mon_date.'" and cw_pay_structure.trans_status = 1 and cw_pay_structure_line.trans_status = 1';
$pay_struct_info = $this->db->query("CALL sp_a_run ('RUN','$pay_struct_qry')");
$pay_struct_rslt = $pay_struct_info->result_array();
$pay_struct_info->next_result();
$pay_struct_arr = array();
array_walk($pay_struct_rslt, function($v, $k) use(&$pay_struct_arr) {
$pay_wbs = $v["wbs_element"];
$pay_pos = $v["position"];
$pay_act_no = $v["activity_no"];
$field_name = $v["hrms_field_name"];
$amount = $v["amount"];
$pay_struct_arr[$pay_wbs][$pay_pos][$pay_act_no][$field_name] = $v;
});
//DR CODE FOR DYNAMICALLY TO CHECK A MONTHLY INPUT PROCESS (BASED ON MI SETTINGS MODULE BASED INPUT)
$mi_imp_check_col_rslt = $this->mi_import_check_col_setting();
//json object to array convertion
$mi_imp_check_col_rslt = json_decode(json_encode($mi_imp_check_col_rslt), true);
foreach($mi_imp_check_col_rslt as $arr){
$mi_imp_check_col_arr[$arr['label_name']] = $arr['label_name'];
}
//array for check a exist mi from mi table based on this array columns
$mi_imp_exist_col_arr = array_map(function ($column) {
return $column['label_name'];
}, $mi_imp_check_col_rslt);
//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 trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and (earn_month_check = "1" OR deduction_month_check = "1")';
$form_info = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_info_rslt = $form_info->result();
$form_info->next_result();
//ARRAY FOR CHECK A PICKLIST DATA ARE VALID OR NOT
$all_pick_import_arr = $this->pay_picklist_get_function("import",$form_info_rslt,"");
//---------------------IMPORTANT CODE FOR MI IMPORT PROCESS END---------------
$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();
for ($row = $excel_row_start; $row <= $total_rows; $row++) {
$exist_val = "";
$pay_struct_exist_val = "";
$prime_column_val = "";
$prime_cell_val = "";
$prime_upd_query = "";
foreach ($excel_format_result as $excel_info) {
$column_name = $excel_info->excel_line_column_name;
$column_value = $excel_info->excel_line_value;
$field_type = (int)$excel_info->field_type;
$get_cell_value = trim($sheet->getCell("$column_value$row")->getCalculatedValue());
if ($column_name === "employee_code"){
$employee_code = $get_cell_value;
//Get Salary Start and End Date;
$emp_data = $emp_code_data[$get_cell_value];
$emp_doj = $emp_data->date_of_joining;
$category = $emp_data->role;
$emp_status = (int)$emp_data->termination_status;
$cost_cen_code = $emp_data->emp_cost_center_code;
if (!array_key_exists($get_cell_value, $emp_code_data ?? [])) {
$error_info[$row] = "Employee Code Not Exist in DB";
}
//EXIST MI CHECK SO EMPLOYEE CODE COLUMN ADD FOR MANDATORY EXIST CHECK PROCESS
$exist_val = $column_name . ' = "' . $get_cell_value . '" and process_month = "' . $process_month . '" and ';
}else
if($field_type === 5 || $field_type === 9){
if(!$get_cell_value){
$show_value = ucwords(str_replace("_"," ",$column_name));
$error_info[$row] = "$show_value Should Not Empty..!";
}else{
if($column_name === "wbs_element"){
$wbs_element = $get_cell_value;
}
if($column_name === "activity_no"){
if($wbs_element){
$get_key_val = $sap_act_no_arr[$wbs_element][$get_cell_value];
if(!$get_key_val){
$error_info[$row] = "Unknown ($get_cell_value) Data Mapped..!";
}
}else{
$error_info[$row] = "Activity Number Column should Map after WBS Element Column.!";
}
$activity_no = $get_key_val;
}else{
$get_key_val = $all_pick_import_arr[$column_name][$get_cell_value];
if(!$get_key_val){
$error_info[$row] = "Unknown ($get_cell_value) Data Mapped..!";
}
if($column_name === "position"){
$position = $get_key_val;
}
}
$get_cell_value = $get_key_val;
}
}else{
//DECIMAL FIELDS ONLY
if($field_type === 2){
//Month Days Validation
if($column_name === "md"){
$month_days = (float)$get_cell_value;
if($month_days > 31){
$error_info[$row] = "Month Days Should Lesser than 31 Days..!";
}else
if($month_days < 0){
$error_info[$row] = "Month Days Should Higher than Zero..!";
}
}
//Payroll and billable employee validation start
if($cost_cen_code === "" || $cost_cen_code === "0" || $cost_cen_code === 0){
if($column_name === "buffer_pd"){
$buffer_pd = number_format((float)$get_cell_value, 2, '.', '');
// $buffer_pd = (float)$get_cell_value;
if($buffer_pd !== "0.00" && $buffer_pd !== 0.00){
$error_info[$row] = "Buffer Days not allow to add for normal Employees.!";
}
}
if($column_name === "pd"){
$paid_days = (float)$get_cell_value;
if(!$month_days){
$error_info[$row] = "Paid Days Column Should Map After Month Days Column";
}else
if(!$month_days){
$error_info[$row] = "Paid Days Column Should Map After Month Days Column";
}else
if($paid_days <= 0.00){
$error_info[$row] = "Paid Days Should not Add Zero.!";
}else
if($paid_days > 31){
$error_info[$row] = "Paid Days Should Add Below 31 Days.!";
}else
if($month_days < $paid_days){
$error_info[$row] = "Paid Days Should Lesser than Month Days.!";
}
}
if($column_name === "reliever_pd"){
$rel_pd_days = (float)$get_cell_value;
if(!$month_days){
$error_info[$row] = "Reliever Days Column Should Map After Month Days Column";
}else
if($rel_pd_days > 31){
$error_info[$row] = "Reliever Days Should Add Below 31 Days..!";
}else
if($month_days < $rel_pd_days){
$error_info[$row] = "Reliever Days Should Lesser than Month Days..!";
}
}
if($column_name === "nb_pd"){
$nb_pd_days = (float)$get_cell_value;
if(!$month_days){
$error_info[$row] = "Non Billable Days Column Should Map After Month Days Column";
}else
if($nb_pd_days > 31){
$error_info[$row] = "Non Billable Days Should Add Below 31 Days..!";
}else
if($month_days < $nb_pd_days){
$error_info[$row] = "Non Billable Days Should Lesser than Month Days..!";
}
}
}else//FOR BILLABLE EMPLOYEES
if($cost_cen_code){
if($column_name === "buffer_pd"){
$buffer_days = (float)$get_cell_value;
if(!$month_days){
$error_info[$row] = "buffer Days Column Should Map After Month Days Column";
}else
if(!$month_days){
$error_info[$row] = "buffer Days Column Should Map After Month Days Column";
}else
if($buffer_days <= 0.00){
$error_info[$row] = "buffer Days Should not Add Zero.!";
}else
if($buffer_days > 31){
$error_info[$row] = "buffer Days Should Add Below 31 Days.!";
}else
if($month_days < $buffer_days){
$error_info[$row] = "buffer Days Should Lesser than Month Days.!";
}
}
if($column_name !== "buffer_pd" && $column_name !== "md"){
$other_input = number_format((float)$get_cell_value, 2, '.', '');
// $other_input = (float)$get_cell_value;
if($other_input !== "0.00" && $other_input !== 0.00){
$error_info[$row] = "For Billable Employees can input only Month Days & Buffer Days.!";
}
}
}
//Payroll and billable employee validation end
//PAY STRUCTURE BASED INPUTS VALIDATE
if($column_name === "ot_hrs"){
$ot_hrs_val = (float)number_format((float)$get_cell_value, 2, '.', '');
}
if($column_name === "ot_days"){
$ot_days_val = (float)number_format((float)$get_cell_value, 2, '.', '');
}
}
}
//Exist Check Condition from mi entry table
if (in_array($column_name, $mi_imp_exist_col_arr)) {
$exist_val .= $column_name . ' = "' . $get_cell_value . '" and ';
if($column_name !== 'project_id'){
$pay_struct_exist_val .= $column_name . ' = "' . $get_cell_value . '" and ';
}
}
}
//SUM OF PAID DAYS CALCULATION FOR PAYROLL EMPLOYEES
$sum_days = $paid_days + $rel_pd_days + $nb_pd_days;
if($cost_cen_code === "" || $cost_cen_code === "0" || $cost_cen_code === 0){
if($month_days < $sum_days){
$error_info[$row] = "Sum of Paid Days Greater than Month Days..!";
}
}
$exist_val = rtrim($exist_val, " and ");
$pay_struct_exist_val = rtrim($pay_struct_exist_val, " and ");
$pay_struct_ext_qry = 'select count(cw_pay_structure.prime_pay_structure_id) exist_count from cw_pay_structure where cw_pay_structure.trans_status = 1 and '.$pay_struct_exist_val.' and cw_pay_structure.from_date <= "'.$process_mon_date.'" and cw_pay_structure.to_date >= "'. $process_mon_date.'"';
// and cw_pay_structure.status = 1
$pay_struct_ext_info = $this->db->query("CALL sp_a_run ('RUN','$pay_struct_ext_qry')");
$pay_struct_ext_rslt = $pay_struct_ext_info->result();
$pay_struct_ext_info->next_result();
$pay_struct_ext_count = $pay_struct_ext_rslt[0]->exist_count;
if(!(int)$pay_struct_ext_count){
$error_info[$row] = "Pay Structure not Mapped for this Combination or Paystructure Apply Date not Applicable for this Process Month.!";
}else{
//Monthly Input Fms exist check for this Month
$exist_query = 'select count(prime_monthly_input_fms_id) exist_count,prime_monthly_input_fms_id,trans_status,entry_status,check_status,payroll_status from cw_monthly_input_fms where cw_monthly_input_fms.trans_status = 1 and '.$exist_val.' and cw_monthly_input_fms.process_month = "'.$process_month.'"';
// echo "<pre> exist_query => $exist_query </br>";
$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 ALREADY EXIST
if($exist_count > 0){
$entry_status = (int)$exist_result[0]->entry_status;
$check_status = (int)$exist_result[0]->check_status;
$payroll_status = (int)$exist_result[0]->payroll_status;
/*if($entry_status === 1 && $check_status === 1){
// || $check_status === 1
$error_info[$row] = "Could not Update.! Checker Status Already Approved.!";
}*/ //commented by sathish
if($payroll_status === 1){
$error_info[$row] = "Could not Update.! Payroll Already Processed..!";
}
}
// ---------------- ELSE CONDTION DON'T DELETE --------
/*else{//IF NOT ALREADY EXIST THEN CHECK BY EMPLOYEE MASTER TABLE
//CURRENT EMPLOYEE MASTER DATA CHECK IF EXIST (CORRECT) IF NOT EXIST (WRONG)
$valid_emp_qry = 'select prime_employees_id from cw_employees where cw_employees.trans_status = 1 and '.$pay_struct_exist_val.' and cw_employees.employee_code = "'. $employee_code.'" and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, "%Y-%m") >= "'.$qry_search_month.'"))';
$valid_emp_info = $this->db->query("CALL sp_a_run ('RUN','$valid_emp_qry')");
$valid_emp_rslt = $valid_emp_info->result();
$valid_emp_info->next_result();
$valid_emp_id = (int)$valid_emp_rslt[0]->prime_employees_id;
if(!$valid_emp_id){
$error_info[$row] = "Pay Structure Combination are not Mapped Correctly for this Employee.!Please Check Employee Master.!";
}
}*/
//OT HOUR RATE CHECK BASED PAY STRUCTURE ARRAY
if(array_key_exists("ot_hrate",$pay_struct_arr[$wbs_element][$position][$activity_no] ?? [])){
$ot_hrate = $pay_struct_arr[$wbs_element][$position][$activity_no]['ot_hrate']['amount'];
$ot_hrate = (float)number_format((float)$ot_hrate, 2, '.', '');
if($ot_hrate === 0.00 && $ot_hrs_val > 0){
$error_info[$row] = "OT Hourly Rate not Available in Pay Structure..!";
}
}
//OT DAY RATE CHECK BASED PAY STRUCTURE ARRAY
if(array_key_exists("ot_drate",$pay_struct_arr[$wbs_element][$position][$activity_no] ?? [])){
$ot_drate = $pay_struct_arr[$wbs_element][$position][$activity_no]['ot_drate']['amount'];
$ot_drate = (float)number_format((float)$ot_drate, 2, '.', '');
if($ot_drate === 0.00 && $ot_days_val > 0){
$error_info[$row] = "OT Days Rate not Available in Pay Structure..!";
}
}
}
}
$can_process_count = count($error_info ?? []);
if ((int) $can_process_count > 0) {
return json_encode(array(
'success' => true,
'message' => "File Has Error",
'error_info' => $error_info
));
}else{
$status_array = array();
$trans_sched_arr = array();
//TRANSACTION PROCESS LIST DATA GET FROM ARRAY
$trans_sched_qry = 'select prime_trans_process_id,wbs_element,total from cw_trans_process_list where cw_trans_process_list.process_month = "'.$process_month.'" and cw_trans_process_list.process_status = "0" and cw_trans_process_list.trans_status = 1';
$trans_sched_info = $this->db->query("CALL sp_a_run ('SELECT','$trans_sched_qry')");
$trans_sched_rslt = $trans_sched_info->result_array();
$trans_sched_info->next_result();
$trans_sched_exist_arr = array_map(function($v){
$return_array = array();
$return_array['wbs_element'] = $v['wbs_element'];
$return_array['sched_data'] = $v;
return $return_array;
}, $trans_sched_rslt);
// echo "<pre>";
// print_r($trans_sched_exist_arr);
$trans_sched_exist_arr = array_column($trans_sched_exist_arr ?? [],'sched_data','wbs_element');
for ($row = $excel_row_start; $row <= $total_rows; $row++) {
$exist_val = "";
$pay_struct_exist_val = "";
$prime_column_val = "";
$prime_cell_val = "";
$status_info = array();
$status_info["Excel Row"] = $row;
$prime_upd_query = "";
foreach ($excel_format_result as $excel_info) {
$column_name = $excel_info->excel_line_column_name;
$column_value = $excel_info->excel_line_value;
$field_type = (int)$excel_info->field_type;
$get_cell_value = trim($sheet->getCell("$column_value$row")->getCalculatedValue());
if ($column_name === "employee_code"){
$employee_code = $get_cell_value;
$emp_data = $emp_code_data[$get_cell_value];
$doj = $emp_data->date_of_joining;
$dob = $emp_data->date_of_birth;
$emp_id = $emp_data->prime_employees_id;
$cat = $emp_data->role;
$emp_name = $emp_data->emp_name;
$emp_status = $emp_data->termination_status;
$emp_cell_val = $emp_id;
//FOR BILLABLE AND PAYROLL EMPLOYEE BASED ENTRY STATUS UPDATE
$cost_cen_code = $emp_data->emp_cost_center_code;
//EXIST MI CHECK SO EMPLOYEE CODE COLUMN ADD FOR MANDATORY EXIST CHECK PROCESS
$exist_val = $column_name . ' = "' . $get_cell_value . '" and process_month = "' . $process_month . '" and ';
}else
if($field_type === 5 || $field_type === 9){
if($column_name === "personal_code"){
$personal_code = $get_cell_value;
}
if($column_name === "project_id"){
$project_id = $get_cell_value;
}
if($column_name === "wbs_element"){
$wbs_element = $get_cell_value;
}
if($column_name === "activity_no"){
if($wbs_element){
$get_cell_value = $sap_act_no_arr[$wbs_element][$get_cell_value];
}
}else{
$get_cell_value = $all_pick_import_arr[$column_name][$get_cell_value];
}
}
//CATE ENTRY STATUS VALUE GET FOR ENTRY STATUS UPDATE END
if($column_name !== "emp_name"){
$prime_column_val .= $column_name . ",";
$prime_cell_val .= '"' . $get_cell_value . '",';
$update_column_val = $column_name;
$update_cell_val = '"' . $get_cell_value . '",';
$prime_upd_query .= $update_column_val . "=" . $update_cell_val;
}
//Exist Check Condition from mi entry table
if (in_array($column_name, $mi_imp_exist_col_arr)) {
$exist_val .= $column_name . ' = "' . $get_cell_value . '" and ';
}
}
$exist_val = rtrim($exist_val, " and ");
//monthly input exist check
$exist_query = 'select count(prime_monthly_input_fms_id) exist_count,prime_monthly_input_fms_id,trans_status,entry_status,check_status from cw_monthly_input_fms where cw_monthly_input_fms.trans_status = 1 and '.$exist_val.' and cw_monthly_input_fms.process_month = "'.$process_month.'"';
$exist_info = $this->db->query("CALL sp_a_run ('RUN','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
$exist_count = $exist_result[0]->exist_count;
//echo "BSK $exist_count <br/>";
//IF EXIST COUNT 0 COULD INSERT A DATA
if((int)$exist_count === 0){
// ------- FUNCTION START FOR INSERT AND UPDATE A TRANS PROCESS LIST TABLE --------
//UPDATE PROCESS
if(array_key_exists($wbs_element,$trans_sched_exist_arr ?? [])){
$trans_pro_id = (int)$trans_sched_exist_arr[$wbs_element]['prime_trans_process_id'];
$tot_count = (int)$trans_sched_exist_arr[$wbs_element]['total'] + 1;
$upd_query = 'UPDATE cw_trans_process_list SET total = "'.$tot_count.'",process_status = "0" WHERE prime_trans_process_id = "'.$trans_pro_id.'" and trans_status = 1';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
}else{ //INSERT PROCESS
$tot_count = 1;
$sched_ins_qry = 'INSERT into cw_trans_process_list(process_month,personal_code,project_id,wbs_element,process_status,total,trans_created_by,trans_created_date) VALUES ("'.$process_month.'","'.$personal_code.'","'.$project_id.'","'.$wbs_element.'","0","'.$tot_count.'","'.$this->logged_id.'","'.$created_on.'")';
$sched_ins_info = $this->db->query("CALL sp_a_run ('INSERT','$sched_ins_qry')");
$sched_ins_rslt = $sched_ins_info->result();
$sched_ins_info->next_result();
$trans_pro_id = (int)$sched_ins_rslt[0]->ins_id;
}
// -------- FUNCTION END FOR INSERT AND UPDATE A TRANS PROCESS LIST TABLE ----------
if(!$trans_pro_id){
$error_info[$row] = "Trans Process Table Insert Error.!";
}else{
//PUSH NEW UPDATED DATA TO TRANS SCHEDULER EXIST ARRAY
$trans_sched_exist_arr[$wbs_element]['prime_trans_process_id'] = $trans_pro_id;
$trans_sched_exist_arr[$wbs_element]['wbs_element'] = $wbs_element;
$trans_sched_exist_arr[$wbs_element]['total'] = $tot_count;
//MI FMS INSERT PROCESS
$prime_column_val .= "employees_id,emp_name,role,date_of_joining,date_of_birth,process_month,trans_created_by,trans_created_date,entry_status,check_status,payroll_status,termination_status,prime_trans_process_id";
$prime_cell_val .= '"'.$emp_cell_val.'","'.$emp_name.'","'.$cat.'","'.$doj.'","'.$dob.'","'.$process_month.'","'.$this->logged_id.'","'.$created_on.'","1","1","2","'.$emp_status.'","'.$trans_pro_id.'"';
$prime_column_val = rtrim($prime_column_val, ",");
$prime_cell_val = rtrim($prime_cell_val, ",");
$prime_query = "insert into cw_monthly_input_fms ($prime_column_val) VALUES ($prime_cell_val)";
$insert_info = $this->db->query("CALL sp_a_run ('RUN','$prime_query')");
$status_info['Status'] = "Inserted to DB";
}
}else //IF EXIST COUNT NOT 0 THEN WE CHECK SOME STATUS AND THEN UPDATE
if ((int)$exist_count > 0){
$upd_prime_id = (int)$exist_result[0]->prime_monthly_input_fms_id;
$entry_status = (int)$exist_result[0]->entry_status;
$check_status = (int)$exist_result[0]->check_status;
$trans_status = (int)$exist_result[0]->trans_status;
if($entry_status !== 1 && $check_status !== 1){
$prime_upd_query .= 'employees_id = "'.$emp_cell_val.'",emp_name = "'.$emp_name.'",role = "'.$cat.'",date_of_joining = "'.$doj.'",date_of_birth = "'.$dob.'",process_month = "'.$process_month.'",trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'",trans_deleted_by = NULL,trans_deleted_date = NULL,entry_status = "1",check_status = "1",payroll_status = "2",termination_status = "'.$emp_status.'"';
if($upd_prime_id){
$upd_fms_query = 'UPDATE cw_monthly_input_fms SET '.$prime_upd_query.' WHERE prime_monthly_input_fms_id = "'.$upd_prime_id.'"';
$upd_fms_info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_fms_query')");
$status_info['Status'] = "Updated to DB";
}
}else{
$status_info['Status'] = "Already Input Processed";
}
}
$status_array[] = $status_info;
}
}
}
//print_r($error_info); die;
$can_process_count = count($error_info ?? []);
if ((int) $can_process_count > 0) {
return json_encode(array(
'success' => true,
'message' => "File Has Error",
'error_info' => $error_info
));
}else{
$table_info = $this->get_excel_import_ui($status_array);
return json_encode(array(
'success' => true,
'message' => "Successfully file imported",
'table_info' => $table_info
));
}
}
}
}
}
?>