File: //home/cafsindia/uds.cafsinfotech.in/application/controllers_bk/Process_payroll_fms.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Process_payroll_fms extends Action_controller{
public function __construct(){
parent::__construct('process_payroll_fms');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$this->create_formula_file();
//PERSONAL AREA
$per_area_rslt = $this->query_build_function('personal_code,personal_name','cw_sap_personal_area','','trans_status = 1 and FIND_IN_SET(personal_code, "'.$this->logged_area_access.'") ');
$per_area_list = "";
foreach($per_area_rslt as $for){
$personal_code = $for['personal_code'];
$personal_name = $for['personal_name'];
if($personal_code !== ""){
$per_area_list .= "<option data-value='".$personal_code."' value='".trim($personal_code)."' >".trim($personal_name)."</option>";
}
}
$data['per_area_list'] = $per_area_list;
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "transactions_fms" 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['module_id'] = "transactions_fms";
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//PROCESS PAYROLL SETTINGS BASED PAYROLL INPUT COLUMNS SELECT
public function payroll_filter_pick_column($type){
$payroll_filter_col_rslt = $this->payroll_filter_col_setting();
$payroll_filter_arr = $this->pay_picklist_get_function("filter",$payroll_filter_col_rslt,$type);
return $payroll_filter_arr;
}
public function payroll_filter_col_setting(){
$payroll_filter_column_qry = 'select prime_form_id,field_type,pick_list,pick_list_type,view_name,pick_table,pick_display_value,label_name,auto_prime_id,auto_dispaly_value,cw_process_payroll_settings.mandatory_column from cw_form_setting inner join cw_process_payroll_settings on cw_process_payroll_settings.map_column = cw_form_setting.label_name where prime_module_id = "employees" and cw_form_setting.trans_status = 1 and cw_process_payroll_settings.trans_status = 1 and cw_process_payroll_settings.filter_column = 1 ORDER BY cw_process_payroll_settings.prime_process_payroll_settings_id ASC';
$payroll_filter_column_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_filter_column_qry')");
$payroll_filter_column_rslt = $payroll_filter_column_data->result();
$payroll_filter_column_data->next_result();
return $payroll_filter_column_rslt;
}
public function pro_mon_payroll_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$process_month = $this->input->post("process_month");
// $process_type = (int)$this->input->post("process_type");
$sel_cols = "";
$join_table = "";
$join_whre_cond = "";
$group_by = "";
$sel_cols = ',cw_sap_personal_area.personal_code,cw_sap_personal_area.personal_name';
$join_table = ' INNER JOIN cw_sap_personal_area ON cw_sap_personal_area.personal_code = cw_transactions_fms.personal_code';
$join_whre_cond = ' and cw_sap_personal_area.trans_status = 1';
$group_by = ' GROUP BY cw_sap_personal_area.personal_code';
$trans_qry = 'select cw_transactions_fms.employee_code,cw_transactions_fms.emp_name'.$sel_cols.' from cw_transactions_fms'.$join_table.' where cw_transactions_fms.trans_status = 1 and cw_transactions_fms.process_month = "'.$process_month.'"'.$join_whre_cond.$group_by;
$trans_info = $this->db->query("CALL sp_a_run ('SELECT','$trans_qry')");
$trans_rslt = $trans_info->result_array();
$trans_info->next_result();
echo json_encode($trans_rslt);
}
//DR CODE START FOR PROJECT ID FETCH BASED ON PERSONAL CODE AND 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');
// $search_month = $this->input->post('search_month');
$process_month = $this->input->post("process_month");
$process_mode = $this->input->post("process_mode");
$delete_type = $this->input->post("delete_type");
$entry_flag = "";
//FOR ACTIVE PROJECT ONLY SHOULD SHOW IN PICKLIST
$start_date = date("Y-m-d",strtotime('01-'.$process_month));
$end_date = date("Y-m-t",strtotime($start_date));
if((int)$process_mode === 3 || ((int)$process_mode === 2 && (int)$delete_type === 1)){//view payroll and delete payroll and transactions
$pro_id_qry = 'SELECT pro_id,pro_desc FROM cw_sap_project WHERE cw_sap_project.pro_personal_area_id = "'.$personal_code.'" and cw_sap_project.pro_start_date <= "'.$start_date.'" and cw_sap_project.pro_end_date >= "'.$end_date.'" and cw_sap_project.trans_status = 1';
}else{
if((int)$delete_type === 2){
$entry_flag = ' and cw_trans_failure_log.r_flag = "C" ';
}else
if((int)$delete_type === 3){
$entry_flag = ' and cw_trans_failure_log.r_flag = "L" ';
}else
if((int)$delete_type === 4){
$entry_flag = ' and cw_trans_failure_log.r_flag = "O" ';
}
$pro_id_qry = 'SELECT cw_sap_project.pro_id,cw_sap_project.pro_desc FROM cw_sap_project INNER JOIN cw_sap_wbs ON cw_sap_wbs.wbs_project_id = cw_sap_project.pro_id INNER JOIN cw_trans_failure_log ON cw_trans_failure_log.wbs_element = cw_sap_wbs.wbs_id WHERE cw_sap_project.pro_personal_area_id = "'.$personal_code.'" AND cw_sap_project.pro_start_date <= "'.$start_date.'" AND cw_sap_project.pro_end_date >= "'.$end_date.'" AND cw_sap_project.trans_status = 1 AND cw_trans_failure_log.process_month="'.$process_month.'" '.$entry_flag.' GROUP BY cw_sap_project.pro_id';
}
// GROUP BY cw_sap_project.pro_id
$pro_id_info = $this->db->query("CALL sp_a_run ('SELECT','$pro_id_qry')");
$pro_id_rslt = $pro_id_info->result_array();
$pro_id_info->next_result();
$pro_list = "<option value = ''>---- Select Project ----</option>";
foreach($pro_id_rslt as $for){
$prime_id = $for['prime_sap_project_id'];
$pro_id = $for['pro_id'];
$pro_desc = $for['pro_desc'];
$pro_list .= "<option value = '$pro_id' data-value = '$pro_id'> $pro_desc</option>";
}
echo $pro_list;
}
//DR CODE FOR WBS ELEMENT FETCH BASED ON PERSONAL AREA AND PROJECT ID AND PROCESS MONTH
public function get_wbs(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
// $search_term = $this->input->post_get('term');
$process_month = $this->input->post('process_month');
$personal_code = $this->input->post('personal_code');
$project_id = $this->input->post('project_id');
$process_mode = (int)$this->input->post('process_mode');
$delete_type = (int)$this->input->post('delete_type');
$start_date = date("Y-m-d",strtotime('01-'.$process_month));
$end_date = date("Y-m-t",strtotime($start_date));
if((int)$process_mode === 3 || ((int)$process_mode === 2 && $delete_type === 1)){
$get_wbs_qry = 'SELECT DISTINCT cw_sap_wbs.prime_sap_wbs_id,cw_sap_wbs.wbs_id,cw_sap_wbs.wbs_desc FROM cw_sap_wbs INNER JOIN cw_transactions_fms ON (cw_transactions_fms.personal_code = cw_sap_wbs.wbs_personal_area_id AND cw_transactions_fms.project_id = cw_sap_wbs.wbs_project_id AND cw_transactions_fms.wbs_element = cw_sap_wbs.wbs_id) WHERE cw_sap_wbs.trans_status = 1 AND cw_sap_wbs.wbs_personal_area_id = "'.$personal_code.'" AND cw_transactions_fms.process_month = "'.$process_month.'" AND cw_transactions_fms.project_id = "'.$project_id.'" AND cw_transactions_fms.personal_code = "'.$personal_code.'" AND cw_transactions_fms.trans_status = 1';
}else{
if((int)$delete_type === 2){
$entry_flag = ' and cw_trans_failure_log.r_flag = "C" ';
}else
if((int)$delete_type === 3){
$entry_flag = ' and cw_trans_failure_log.r_flag = "L" ';
}else{
$entry_flag = ' and cw_trans_failure_log.r_flag = "O" ';
}
$get_wbs_qry = 'SELECT DISTINCT cw_sap_wbs.prime_sap_wbs_id,cw_sap_wbs.wbs_id,cw_sap_wbs.wbs_desc FROM cw_sap_wbs INNER JOIN cw_trans_failure_log ON cw_trans_failure_log.wbs_element = cw_sap_wbs.wbs_id WHERE cw_sap_wbs.trans_status = 1 AND cw_trans_failure_log.personal_code = "'.$personal_code.'" AND cw_sap_wbs.wbs_project_id ="'.$project_id.'" AND cw_trans_failure_log.process_month="'.$process_month.'" '.$entry_flag.' '; //GROUP BY cw_sap_wbs.wbs_id
}
$get_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$get_wbs_qry')");
$get_wbs_rslt = $get_wbs_info->result_array();
$get_wbs_info->next_result();
$wbs_list = "<option value = ''>---- Select Wbs ----</option>";
foreach($get_wbs_rslt as $for){
$prime_id = $for['prime_sap_wbs_id'];
$wbs_id = $for['wbs_id'];
$wbs_desc = $for['wbs_desc'];
$wbs_list .= "<option value = '$wbs_id' data-value = '$wbs_id'> $wbs_desc</option>";
}
echo $wbs_list;
}
//DR CODE START FOR PROCESS MONTH BASED EMPLOYEE CODE GET
public function get_emp_code(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
// $search_term = $this->input->post_get('term');
$process_month = $this->input->post('process_month');
$personal_code = $this->input->post('personal_code');
$project_id = $this->input->post('project_id');
$wbs_element = $this->input->post('wbs_element');
$process_mode = (int)$this->input->post('process_mode');
$delete_type = (int)$this->input->post('delete_type');
if((int)$process_mode === 3 || ((int)$process_mode === 2 && $delete_type === 1)){ //view payroll or delete payroll and transactions
$emp_qry = 'SELECT cw_transactions_fms.emp_name,cw_transactions_fms.employee_code
from cw_transactions_fms where cw_transactions_fms.trans_status = 1 and cw_transactions_fms.process_month = "'.$process_month.'" and cw_transactions_fms.personal_code = "'.$personal_code.'" and cw_transactions_fms.project_id = "'.$project_id.'" and cw_transactions_fms.wbs_element = "'.$wbs_element.'" GROUP BY cw_transactions_fms.employee_code';
}else{
if((int)$delete_type === 2){
$entry_flag = ' and cw_trans_failure_log.r_flag = "C" ';
}else
if((int)$delete_type === 3){
$entry_flag = ' and cw_trans_failure_log.r_flag = "L" ';
}else{
$entry_flag = ' and cw_trans_failure_log.r_flag = "O" ';
}
$emp_qry = 'SELECT cw_employees.emp_name,cw_employees.employee_code FROM cw_employees
INNER JOIN cw_trans_failure_log ON cw_trans_failure_log.employee_code = cw_employees.employee_code
INNER JOIN cw_sap_wbs ON cw_sap_wbs.wbs_id= cw_trans_failure_log.wbs_element WHERE cw_sap_wbs.wbs_project_id = "'.$project_id.'" AND cw_trans_failure_log.personal_code="'.$personal_code.'" AND cw_trans_failure_log.wbs_element="'.$wbs_element.'" AND cw_trans_failure_log.process_month="'.$process_month.'"'.$entry_flag;
}
$trans_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$trans_rslt = $trans_info->result_array();
$trans_info->next_result();
$emp_list = "<option value = ''>---- Select Employee ----</option>";
foreach($trans_rslt as $for){
$emp_code = $for['employee_code'];
$emp_name = $for['emp_name'];
$emp_list .= "<option value = '$emp_code ~ $emp_name' data-value = '$emp_code'> $emp_name</option>";
}
echo $emp_list;
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
public function process_payroll_fms(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$post_data = $this->input->post();
unset($post_data['process_month']);
unset($post_data['process_mode']);
// unset($post_data['process_type']);
// unset($post_data['employee_code']);
$filter_qry = http_build_query($post_data, '', ',');
// $filter_qry
if(!$filter_qry){
echo json_encode(array('success' => true,'message' => "Monthly Input Filter Setting not Mapped Please Map Filter Setting Columns..."));
exit(0);
}else{
$payroll_filter_setting_rslt = $this->payroll_filter_col_setting();
$filter_qry = "";
foreach($payroll_filter_setting_rslt as $setting){
$label_name = $setting->label_name;
$label_id = $this->input->post("$label_name");
$mandatory_col = (int)$setting->mandatory_column;
if($label_id){
$filter_qry .= ' and cw_transactions_fms.'.$label_name.' = "' . $label_id . '"';
}
}
}
$process_month = $this->input->post("process_month");
$personal_code = $this->input->post('personal_code');
$project_id = $this->input->post('project_id');
$wbs_element = $this->input->post('wbs_element');
// $wbs_element = $wbs_element[0];
$employee_code = $this->input->post('employee_code');
$emp_split = explode('~', $employee_code ?? "");
$employee_code = trim($emp_split[0]);
// $employee_code = $employee_code[0];
$process_mode = (int)$this->input->post("process_mode");
$delete_type = (int)$this->input->post('delete_type');
$logged_id = $this->logged_id;
$filter_qry = ' and cw_transactions_fms.personal_code = "'.$personal_code.'" and cw_transactions_fms.project_id = "'.$project_id.'"';
$del_filter_qry = ' and cw_trans_failure_log.personal_code = "'.$personal_code.'"';
if($wbs_element){
$filter_qry .= ' and cw_transactions_fms.wbs_element = "'.$wbs_element.'"';
$del_filter_qry .= ' and cw_trans_failure_log.wbs_element="'.$wbs_element.'"';
}
if($employee_code){
$filter_qry .= ' and cw_transactions_fms.employee_code = "'.$employee_code.'"';
$del_filter_qry .= ' and cw_trans_failure_log.employee_code = "'.$employee_code.'"';
}
if($process_mode === 2){//DELETE PAYROLL
if($delete_type === 1){ //transactions employees
$sap_trans_sts_chk = 'select count(*) as status_count from cw_transactions_fms where cw_transactions_fms.process_month = "'.$process_month.'" '.$filter_qry.'';
}else{
if((int)$delete_type === 2){
$entry_flag = ' and cw_trans_failure_log.r_flag = "C" ';
}else
if((int)$delete_type === 3){
$entry_flag = ' and cw_trans_failure_log.r_flag = "L" ';
}else{
$entry_flag = ' and cw_trans_failure_log.r_flag = "O" ';
}
$sap_trans_sts_chk = 'select count(*) as status_count from cw_trans_failure_log
INNER JOIN cw_pre_audit_salary ON cw_pre_audit_salary.wbs_element = cw_trans_failure_log.wbs_element WHERE cw_pre_audit_salary.project_id = "'.$project_id.'" AND cw_trans_failure_log.process_month="'.$process_month.'" AND cw_pre_audit_salary.process_month="'.$process_month.'" AND cw_pre_audit_salary.audit_status= 1 and cw_trans_failure_log.trans_status = 1 '.$entry_flag.$del_filter_qry;
}
$trans_sts_info = $this->db->query("CALL sp_a_run ('SELECT','$sap_trans_sts_chk')");
$trans_sts_rslt = $trans_sts_info->result_array();
$trans_sts_info->next_result();
$sap_trans_count = (int)$trans_sts_rslt[0]['status_count'];
if($sap_trans_count === 0){
echo json_encode(array('success' => FALSE,'message' => "No Records Found.!"));
exit(0);
}else{
$save_result = $this->db->query("CALL itsp_delete_prcpay('$project_id','$wbs_element','$process_month','$employee_code','$logged_id','$delete_type')");
$save_result_rslt = $save_result->result();
$save_result->next_result();
if((int)$save_result_rslt[0]->result === 1){
$table_view = $this->transaction_data($process_month,$filter_qry);
$tbl_count = (int)$table_view['table_count'];
$tbl_view = $table_view['table_content'];
if($tbl_count === $sap_trans_count){
echo json_encode(array('success' => FALSE,'message' => "There is no data to delete!"));
exit(0);
}else{
if($tbl_view){
echo json_encode(array('success' => TRUE,'message' =>"Payroll Process Deleted.The following records display below can't delete as it neither failed nor reversed.",'table_content' => $tbl_view));
exit(0);
}else{
echo json_encode(array('success' => TRUE,'message' =>"Payroll Process Deleted.",'table_content' => $tbl_view));
exit(0);
}
}
}else
if((int)$save_result_rslt[0]->result === 2){
echo json_encode(array('success' => FALSE,'message' => "Future Month Payroll Already Exist.. PLease Reverse the Future month and try again..!"));
exit(0);
}else
if((int)$save_result_rslt[0]->result === 3){
$table_view = $this->transaction_data($process_month,$filter_qry);
$tbl_view = $table_view['table_content'];
echo json_encode(array('success' => FALSE,'message' => "Arrear has been processed for this combination.Kindly delete it and process again.!",'table_content' => $tbl_view));
exit(0);
}
}
}else
if($process_mode === 3){//VIEW PAYROLL
$table_view = $this->transaction_data($process_month,$filter_qry);
$tbl_view = $table_view['table_content'];
if(!$tbl_view){
echo json_encode(array('success' => FALSE, 'message' =>"No Records Found.!"));
}else{
echo json_encode(array('success' => TRUE, 'message' =>"See payroll details.!",'table_content' =>$tbl_view));
}
}
}
//SELECT PAYROLL DATA IN TABLE FORMAT
public function transaction_data($process_month,$filter_qry){
$form_setting_query = 'SELECT prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "employees" and input_view_type in (1,2) and transaction_type in (1,2,3) and (earn_payroll_check = "1" or ded_payroll_check = "1") and trans_status = "1" order by payroll_sort asc';
$form_setting_info = $this->db->query("CALL sp_a_run ('SELECT','$form_setting_query')");
$form_setting_rslt = $form_setting_info->result();
$form_setting_info->next_result();
$table_name = "cw_transactions_fms";
$thead_line = "";
$thead = "";
$select_query = "";
$pick_query = "";
foreach($form_setting_rslt as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
if($label_name == "role"){
$view_name = "Category";
}
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else{
$select_query .= "$table_name.$label_name , ";
}
if((int)$i === 1){
$th_style = "class='hard_left'";
}else
if((int)$i === 2){
$th_style = "class='next_left'";
}else{
$th_style = "";
}
$thead_line .= "<th $th_style>$view_name</th>";
$i++;
}
$thead = "<tr>$thead_line</tr>";
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
$trans_status = " left join cw_employees on cw_employees.employee_code = cw_transactions_fms.employee_code where ".$table_name.".trans_status = 1 and cw_transactions_fms.process_month = \"".$process_month."\"";
$final_qry = "select $select_query $query_key from $table_name $pick_query $trans_status $filter_qry";
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
$final_count = count($final_result ?? []);
$table_content = "";
if(empty($final_result)){
return 1; // [MS 12-12-2024]
}else{
$tr_line = "";
foreach($final_result as $rslt){
$td_line = "";
$j = 1;
foreach($rslt as $value){
if((int)$j === 1){
$cls = "class='hard_left'";
}else
if((int)$j === 2){
$cls = "class='next_left'";
}else{
$cls = "";
}
$td_line .= "<td $cls>$value</td>";
$j++;
}
$tr_line .= "<tr>$td_line</tr>";
}
$table_content = "<div class='outer'><div class='inner'><table class='table table-bordered display' id='detail_list' style='box-shadow:none;'>
<thead>
$thead
</thead>
<tbody>
$tr_line
</tbody>
</table></div></div>";
$result = array('table_content' => $table_content,'table_count'=>$final_count);
return $result;
}
}
//CREATE_FORMULA_FILE
public function create_formula_file(){ //file path with server
$filename = dirname(__FILE__)."/"."Payroll_calculation_model.php";
$filename = str_replace('controllers','models',$filename);
if(file_exists($filename)){
$created_date = date("Y-m-d H:i:s",filemtime($filename));
$isupdated_qry = 'SELECT count(*) as tot_count FROM cw_payroll_formula WHERE trans_created_date >= "'.$created_date.'" or trans_updated_date >= "'.$created_date.'"';
$isupdated_data = $this->db->query("CALL sp_a_run ('SELECT','$isupdated_qry')");
$isupdated_result = $isupdated_data->result();
$isupdated_data->next_result();
$tot_count = (int)$isupdated_result[0]->tot_count;
$loan_updated_qry = 'SELECT count(*) as tot_count FROM cw_loan WHERE trans_created_date >= "'.$created_date.'" or trans_updated_date >= "'.$created_date.'"';
$loan_updated_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_updated_qry')");
$loan_updated_result = $loan_updated_data->result();
$loan_updated_data->next_result();
$loan_tot_count = (int)$loan_updated_result[0]->tot_count;
if((int)$tot_count > 0 || (int)$loan_tot_count > 0){
$can_process = true;
}else{
$can_process = false;
}
}else{
$can_process = true;
//END Total Earnings and Total Deduction Auto Calculate
}
//$can_process = true;
//get function name and map column and input value
$statutory_map_qry = 'select map_column,statutory_function_name as function_name,input_column from cw_payroll_function inner join cw_statutory_function on cw_statutory_function.prime_statutory_function_id =cw_payroll_function.function_name where cw_payroll_function.trans_status = 1';
$statutory_map_data = $this->db->query("CALL sp_a_run ('SELECT','$statutory_map_qry')");
$statutory_map_result = $statutory_map_data->result();
$statutory_map_data->next_result();
$statutory_map_list = array();
foreach($statutory_map_result as $statutory_map){
$map_column = $statutory_map->map_column;
$function_name = $statutory_map->function_name;
$input_column = $statutory_map->input_column;
$statutory_map_list[$map_column] = array('map_column'=>$map_column,'function_name'=> $function_name, 'input_column'=>$input_column, 'sts'=>false);
}
if($can_process){
$map_qry = 'select formula_for,out_column,payroll_formula,formula_mode from cw_payroll_formula where cw_payroll_formula.trans_status = 1';
$data = $this->db->query("CALL sp_a_run ('SELECT','$map_qry')");
$map_result = $data->result();
$data->next_result();
$map_list = array();
foreach($map_result as $map_result_map){
$formula_for = $map_result_map->formula_for;
$out_column = $map_result_map->out_column;
$payroll_formula = $map_result_map->payroll_formula;
$formula_mode = $map_result_map->formula_mode;
$this->sort_formula($formula_for,$out_column,$payroll_formula,$formula_mode);
}
$oldmask = umask(0);
$formula_temp_file = dirname(__FILE__)."/"."Payroll_calculation_model.php";
$formula_temp_file = str_replace('controllers','models',$formula_temp_file);
fopen("$formula_temp_file", "w");
file_put_contents("$formula_temp_file",$final_code);
chmod($formula_temp_file, 0777);
umask($oldmask);
}
}
//sorting formula updates
public function sort_formula($formula_for,$out_column,$payroll_formula,$formula_mode){
$preg_match_inputs = preg_match_all('#\@(.*?)\@#', $payroll_formula,$preg_match_inputsvalue);
$preg_match_inputsvalue_count = count($preg_match_inputsvalue[1] ?? []);
$input_match_column = implode('","',$preg_match_inputsvalue[1] ?? []);
$input_match_column ='"'.$input_match_column.'"';
$qu_find_sortorder='select IFNULL(MIN(formula_order), 0) as formula_order_min, IFNULL(MAX(formula_order), 0) as formula_order_max from cw_payroll_formula where formula_for = "'.$formula_for.'" and (out_column="'.$out_column.'" or out_column in ('.$input_match_column.')) and trans_status = 1 order by formula_order desc';
$max_min_data = $this->db->query("CALL sp_a_run ('SELECT','$qu_find_sortorder')");
$max_min_rslt = $max_min_data->result();
$max_min_data->next_result();
$outcolum_maxorder = $max_min_rslt[0]->formula_order_max;
$outcolum_minorder = $max_min_rslt[0]->formula_order_min;
$outcolum_order = $outcolum_maxorder;
$min = $outcolum_minorder;
$qu_find_sortorder_data='select out_column,formula_order from cw_payroll_formula where formula_for = "'.$formula_for.'" and (formula_mode !=1 or out_column="'.$out_column.'") and formula_order between "'.$min.'" and "'.$outcolum_maxorder.'" and trans_status = 1 order by formula_order asc';
$max_min_sort_data = $this->db->query("CALL sp_a_run ('SELECT','$qu_find_sortorder_data')");
$max_min_sort_rslt = $max_min_sort_data->result();
$max_min_sort_data->next_result();
foreach ($max_min_sort_rslt as $result){
$out_column_db = $result->out_column;
$formula_order = $result->formula_order;
if($out_column==$out_column_db){
if((int)$formula_mode === 1){
$upd_sort = 'UPDATE cw_payroll_formula SET formula_order = 1 where formula_for = "'.$formula_for.'" and out_column = "'.$out_column.'" and trans_status = 1';
}else{
$upd_sort = 'UPDATE cw_payroll_formula SET formula_order = "'.$outcolum_order.'" where formula_for = "'.$formula_for.'" and out_column = "'.$out_column.'" and trans_status = 1';
}
$this->db->query("CALL sp_a_run ('RUN','$upd_sort')");
}else{
$upd_sort = 'UPDATE cw_payroll_formula SET formula_order = "'.$min.'" where formula_for = "'.$formula_for.'" and out_column="'.$out_column_db.'" and formula_order="'.$formula_order.'" and formula_mode !=1 and trans_status = 1';
$this->db->query("CALL sp_a_run ('RUN','$upd_sort')");
$min++;
}
}
if((int)$formula_mode !== 1){
$find_max_order_qry ='select IFNULL(MAX(formula_order), 0) as max_order from cw_payroll_formula where formula_for = "'.$formula_for.'" and trans_status = 1 and out_column != "net_pay" order by formula_order desc';
$max_order_data = $this->db->query("CALL sp_a_run ('SELECT','$find_max_order_qry')");
$max_order_rslt = $max_order_data->result();
$max_order_data->next_result();
$max_order = $max_order_rslt[0]->max_order;
$i = (int)$max_order + 1;
if($max_order){
$upd_sort_net = 'UPDATE cw_payroll_formula SET formula_order = "'.$i.'" where formula_for = "'.$formula_for.'" and out_column = "net_pay"';
$this->db->query("CALL sp_a_run ('RUN','$upd_sort_net')");
}
}
}
// Sheet Name display in import page
public function sheet_name(){
$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);
}
$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));
}
//excel maaping format
public function fms_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 excel_name,excel_line_column_name,excel_line_value from cw_util_excel_format_line 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 excel_line_column_name';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$get_view_name_query = 'SELECT view_name,label_name FROM `cw_form_setting` WHERE trans_status = 1 and prime_module_id = "employees"';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_view_name_query')");
$column_rslt = $column_info->result_array();
$column_info->next_result();
$column_rslt = array_reduce($column_rslt ?? [], function ($result, $arr) {
$result[$arr['label_name']] = $arr['view_name'];
return $result;
}, array());
$excel_name = str_replace(' ', '_', $excel_result[0]->excel_name);
require_once APPPATH."/controllers/php_excel/PHPExcel.php";
$obj = new PHPExcel();
//Set the first row as the header row
foreach($excel_result as $excel){
$view_name = $column_rslt[$excel->excel_line_column_name];
$col_name = $excel->excel_line_column_name;
$excel_line_value = $excel->excel_line_value;
if(!$view_name){
$view_name = str_replace('_', ' ', $excel->excel_line_column_name);
}
$obj->getActiveSheet()->setCellValue($excel_line_value."1", $view_name);
}
// Rename worksheet name
$filename= $module_id.".xls"; //save our workbook as this file name
ob_end_clean();
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
ob_end_clean();
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$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));
}
//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('transaction_month');
$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');
$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) VALUES ("'.$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_excel_payroll_import($import_id,$process_month);
}
//IMPORT DATA FROM FILE PATH
public function do_excel_payroll_import($import_id,$process_month){
$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{
$this->prime_table = "cw_transactions_fms";
$excel_file_path = $excel_path_result[0]->excel_file_path;
$module_id = "transactions_fms";
$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;
$lock_pay_qry = 'SELECT * FROM cw_payroll WHERE pay_month = "'.$process_month.'" AND status = 1 AND trans_status = 1';
$lock_pay_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_pay_qry')");
$lock_num_rows = $lock_pay_data->num_rows();
$lock_pay_data->next_result();
if((int)$lock_num_rows > 0){
return json_encode(array('success' => false, 'message' => "payroll is locked for 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 pick_list_import,mandatory_field,date_type,prime_module_id,date_type,unique_field,pick_list_import,view_name,label_name,field_type,pick_table,pick_list_type,pick_list,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 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();
//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 cw_form_setting.prime_module_id IN ("employees") AND transaction_type != 4 AND field_type != 10 AND cw_form_setting.trans_status = "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->get_pick_list_data($form_info_rslt);
//CHECKING VALIDAITON
$columnwise_result = $this->excel_columnwise_validation($sheet,$excel_obj,$import_type,$excel_format_result,$excel_row_start,$total_rows,$module_id,$unq_result,$process_month,$all_pick_import_arr);
$err_column_array = $columnwise_result['err_column_array'];
$err_column_tabview = $columnwise_result['err_column_tabview'];
$err_column_count = count($err_column_array['error'] ?? []);
$err_column = implode(",",(array_unique($err_column_array['error'] ?? [])) ?? []);
if((int)$err_column_count > 0){
$table_info = $this->get_excel_error_ui($err_column_tabview);
echo json_encode(array('success'=>false,'message'=>"Column Wise Error",'table_info'=>$table_info));
exit(0);
}else{
$rowwise_result = $this->excel_rowwise_validation($sheet,$excel_obj,$import_type,$excel_format_result,$excel_row_start,$total_rows,$module_id,$unq_result,$process_month,$all_pick_import_arr);
$err_column_array = $rowwise_result['err_column_array'];
$err_column_tabview = $rowwise_result['err_column_tabview'];
$err_column_count = count($err_column_array['error'] ?? []);
$err_column = implode(",",(array_unique($err_column_array['error'] ?? [])) ?? []);
if((int)$err_column_count > 0){
$table_info = $this->get_excel_error_ui($err_column_tabview);
echo json_encode(array('success'=>false,'message'=>"Row wise Error",'table_info'=>$table_info));
exit(0);
}else{
// IMPORT TO DB
$final_result = $this->final_excel_import($module_id,$sheet,$import_type,$excel_format_result,$excel_row_start,$total_rows,$exist_column_name,$process_month,$all_pick_import_arr);
}
}
}
}
}
}
//COLUMNWISE VALIDATION.
public function excel_columnwise_validation($sheet,$excel_obj,$import_type,$excel_format_result,$excel_row_start,$total_rows,$module_id,$unq_result,$process_month,$all_pick_import_arr){
$err_column_tabview = array();
$array_uniq = array();
//EMPLOYEE EXIST VALIDATION
$new_qry = 'SELECT employee_code FROM cw_employees WHERE trans_status = 1 and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m") <= DATE_FORMAT(str_to_date("01-'.$process_month.'", "%d-%m-%Y") , "%Y-%m") and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, "%Y-%m") >= DATE_FORMAT(str_to_date("01-'.$process_month.'", "%d-%m-%Y") , "%Y-%m")))';
$new_info = $this->db->query("CALL sp_a_run ('SELECT','$new_qry')");
$new_rslt = $new_info->result_array();
$new_info->next_result();
$new_emp_code = array_column($new_rslt ?? [], 'employee_code');
foreach($excel_format_result as $key => $value){
$mandatory_field = (int)$value->mandatory_field;
$label_name = $value->label_name;
$view_name = $value->view_name;
$unique_field = $value->unique_field;
$field_type = (int)$value->field_type;
$pick_table = $value->pick_table;
$pick_list_type = (int)$value->pick_list_type;
$excel_line_value = $value->excel_line_value;
$pick_list_import = (int)$value->pick_list_import;
$pick_list = $value->pick_list;
$module_id = $value->prime_module_id;
$get_cell_value = $sheet->rangeToArray("$excel_line_value$excel_row_start:$excel_line_value$total_rows", NULL, TRUE, TRUE, TRUE);
$i = $excel_row_start;
$excel_line_column_name = $value->excel_line_column_name;
foreach($get_cell_value as $common_value){
foreach($common_value as $col_key =>$col_value){
if($excel_line_column_name === "employee_code"){
$employee_code = $col_value;
if(!in_array($employee_code,$new_emp_code)){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "Invalid - [$employee_code] is not exist..!";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
if($trans_rslt[$employee_code] === '2'){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "[$employee_code] - Record has already been imported for the month.";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}
//EMPTY COLUMN VALUE
if(empty($col_value) && !is_numeric($col_value) && $mandatory_field === 1){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = ": Invalid - Empty Columns and Invalid Data - Please verify the data..!";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}else{
if($field_type === 2){ // FOR DECIMAL
$excel_float = $sheet->getCell("$col_key$i")->getValue();
if(!is_numeric($excel_float) && !empty($excel_float)){
if((int)$this->validateDecimal($excel_float) === 0) {
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "[$col_value] - Invalid... Please map the correct Decimal";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name . " " . $msg_line;
}
}
}elseif($field_type === 3){ // FOR INTEGER
$excel_int = $sheet->getCell("$col_key$i")->getValue();
if(!empty($excel_int)){
if(!is_numeric($excel_int)){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "[$col_value] - Invalid... Please map The correct number";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}
}elseif($field_type === 4){ // FOR DATE
$cell = $sheet->getCell("$col_key$i");
$excelDateValue = $cell->getValue();
# Convert the raw date value to a PHP date object
$dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($excelDateValue);
$formattedDate = $dateObj->format('d-m-Y');
if($date_type === 1){ //DATE-MONTH-YEAR
if(!empty($formattedDate)){
if((int)$this->validateDOB($formattedDate) === 0){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "[$col_value]: Invalid - Please use the correct Date format (DD-MM-YYYY).!";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}
}
}elseif(($field_type === 5) || ($field_type === 7)){ // FOR PICKLIST
// if($all_pick_import_arr[$module_id][$pick_list_type][$label_name]){
foreach($all_pick_import_arr as $pick){
if($pick[$module_id][$label_name]){
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($excel_line_column_name === "role" && $pick_list_type === 1){
if(in_array($col_value, $pick[$module_id][$label_name][$pick_list_val_2])){
$category= array_search($col_value, $pick[$module_id][$label_name][$pick_list_val_2] ?? []);
}
}
if($pick_list_import === 1 && !in_array($col_value,$pick[$module_id][$label_name][$pick_list_val_1])){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "Invalid Data[$col_value] is Present Please Check it.!";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
if($pick_list_import === 2 && !in_array($col_value,$pick[$module_id][$label_name][$pick_list_val_2])){
$err_column_array['error']["$excel_line_value$i"] = $view_name;
$msg_line = "Invalid Data[$col_value] is Present Please Check it.!";
$err_column_tabview['error']["$excel_line_value$i"] = $view_name." ".$msg_line;
}
}
}
}
}
$i++;
}
}
}
$check_array = array("err_column_array" => $err_column_array,"err_column_tabview" => $err_column_tabview);
return $check_array;
}
//ROW WISE VALIDATION
public function excel_rowwise_validation($sheet,$excel_obj,$import_type,$excel_format_result,$excel_row_start,$total_rows,$module_id,$unq_result,$process_month,$all_pick_import_arr){
//PAY ST COMBINATION
$start_date = date("Y-m-t",strtotime('01-'.$process_month));
$end_date = date("Y-m-d",strtotime('01-'.$process_month));
//status = 1 and
$pay_qry = 'select wbs_element,position,category,status from cw_pay_structure where from_date <= "'.$start_date.'" and to_date >= "'.$end_date.'"';
$pay_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_qry')");
$pay_rslt = $pay_info->result_array();
$pay_info->next_result();
$emp_fil_rslt = array();
foreach($pay_rslt as $arr){
$emp_fil_rslt[$arr['position']][$arr['wbs_element']][$arr['category']] = $arr['status'];
}
//SUCCESS RECORDS SHOULD NOT ALLOW TO IMPORT
$trans_qry = 'SELECT employee_code,position,wbs_element,role,sap_trans_status FROM '.$this->prime_table.' WHERE transactions_month = "'.$process_month.'" AND sap_trans_status = 2 ';
$trans_info = $this->db->query("CALL sp_a_run ('SELECT','$trans_qry')");
$trans_rslt = $trans_info->result_array();
$trans_info->next_result();
$trans_data = array();
foreach($trans_rslt as $trans){
$trans_data[$trans['employee_code']][$trans['position']][$trans['wbs_element']][$trans['role']] = $trans['sap_trans_status'];
}
for($row = $excel_row_start; $row <= $total_rows;$row++){
foreach($excel_format_result as $excel_info){
$label_name = $excel_info->label_name;
$field_type = (int)$excel_info->field_type;
$text_type = (int)$excel_info->text_type;
$excel_line_column_name = $excel_info->excel_line_column_name;
$excel_line_value = $excel_info->excel_line_value;
$view_name = $excel_info->view_name;
$pick_list_type = (int)$excel_info->pick_list_type;
$pick_list = $excel_info->pick_list;
$pick_table = $excel_info->pick_table;
$pick_list_import = (int)$excel_info->pick_list_import;
$mandatory_field = (int)$excel_info->mandatory_field;
$field_length = (int)$excel_info->field_length;
$module_id = $excel_info->prime_module_id;
$get_cell_value = trim($sheet->getCell("$excel_line_value$row")->getCalculatedValue());
if($excel_line_column_name === "employee_code"){
$employee_code = $get_cell_value;
}
if($excel_line_column_name === "position"){
$position = $get_cell_value;
}
if($excel_line_column_name === "wbs_element"){
$wbs_element = $get_cell_value;
}
if($excel_line_column_name === "role" ){
foreach($all_pick_import_arr as $pick){
if($pick[$module_id][$label_name]){
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($pick_list_type === 1){
if(in_array($get_cell_value, $pick[$module_id][$label_name][$pick_list_val_2])){
$category= array_search($get_cell_value, $pick[$module_id][$label_name][$pick_list_val_2] ?? []);
}
}
}
}
}
}
//PAY ST COMBINATION VALIDATION
if($wbs_element && $position && $category){
if($trans_data[$employee_code][$position][$wbs_element][$category] === '2'){
$err_column_array['error']["$row"] = $view_name;
$msg_line = "payroll has been posted for this combination.Kindly check!";
$err_column_tabview['error']["$row"] = $msg_line;
}elseif(!$emp_fil_rslt[$position][$wbs_element]){
$err_column_array['error']["$row"] = $view_name;
$msg_line = " Paystructure not Available for this WBS and Position...";
$err_column_tabview['error']["$row"] = $msg_line;
}elseif(!$emp_fil_rslt[$position][$wbs_element][$category]){
$err_column_array['error']["$row"] = $view_name;
$msg_line = " Paystructure not Available for this Activity No and Category...";
$err_column_tabview['error']["$row"] = $msg_line;
}
}
}
$check_array = array("err_column_array" => $err_column_array,"err_column_tabview" => $err_column_tabview);
return $check_array;
}
//FINAL IMPORT
public function final_excel_import($module_id,$sheet,$import_type,$excel_format_result,$excel_row_start,$total_rows,$exist_column_name,$process_month,$all_pick_import_arr){
$insert_data = array();
$update_data = array();
$created_on = date("Y-m-d H:i:s");
for($row = $excel_row_start; $row <= $total_rows; $row++){
$prime_column_val = "";
$prime_cell_val = "";
foreach($excel_format_result as $excel_info){
$label_id = $excel_info->label_name;
$module_id = $excel_info->prime_module_id;
$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;
$excel_line_column_name = $excel_info->excel_line_column_name;
$excel_line_value = $excel_info->excel_line_value;
$pick_list_import = (int)$excel_info->pick_list_import;
$get_cell_value = trim($sheet->getCell("$excel_line_value$row")->getCalculatedValue());
// FOR DATE
if($field_type === 4){
$get_cell_value = trim(date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell("$excel_line_value$row")->getCalculatedValue())));
}
if(($field_type === 5) || ($field_type === 7)){ // FOR PICKLIST
foreach($all_pick_import_arr as $pick){
if($pick[$module_id][$label_id]){
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($pick_list_import === 2 && $pick_list_type === 1){
if(in_array($get_cell_value, $pick[$module_id][$label_id][$pick_list_val_2])){
$get_cell_value = array_search($get_cell_value, $pick[$module_id][$label_id][$pick_list_val_2] ?? []);
}
}
}
}
}
if($field_isdefault === 1){
if($excel_line_column_name === "employee_code"){
$employee_code = $get_cell_value;
}
if($excel_line_column_name === "wbs_element"){
$wbs_element = $get_cell_value;
}
if($excel_line_column_name === "position"){
$position = $get_cell_value;
}
$prime_column_val .= $excel_line_column_name.",";
$prime_cell_val .= '"'.$get_cell_value.'",';
}
}
//FOR DUPLICATE RECORDS.
$key = $employee_code . '-' . $wbs_element . '-' . $position;
$check_query = 'SELECT COUNT(*) AS count,prime_transactions_fms_id as prime_id FROM '.$this->prime_table.' WHERE employee_code = "'.$employee_code.'" AND wbs_element = "'.$wbs_element.'" AND position = "'.$position.'" AND transactions_month = "'.$process_month.'" ';
$check_info = $this->db->query("CALL sp_a_run ('SELECT','$check_query')");
$check_result = $check_info->result();
$check_info->next_result();
$prime_transactions_fms_id = $check_result[0]->prime_id;
$check_count = (int)$check_result[0]->count;
if($check_count > 0){
//UPDATE
$update_data[$key] = array('prime_id' => $prime_transactions_fms_id,'employee_code' => $employee_code,'wbs_element' => $wbs_element,'position' => $position,'prime_column_val' => rtrim($prime_column_val, ","),'prime_cell_val' => rtrim($prime_cell_val, ","));
}else{
//INSERT
$insert_data[$key] = array('employee_code' => $employee_code,'wbs_element' => $wbs_element,'position' => $position,'prime_column_val' => rtrim($prime_column_val, ","),'prime_cell_val' => rtrim($prime_cell_val, ","));
}
}
//BULK INSERT
if($insert_data){
$insert_values = array();
$insert_query = 'INSERT INTO '.$this->prime_table.' ('.$insert_data[key($insert_data)]['prime_column_val'].', process_month,transactions_month,trans_updated_by, trans_updated_date) VALUES ';
foreach($insert_data as $data){
$insert_values[] = '('.$data['prime_cell_val'].', "'.$process_month.'", "'.$process_month.'","'.$this->logged_id.'", "'.$created_on.'")';
}
$insert_query .= implode(',', $insert_values ?? []);
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
}
//UPDATE
foreach($update_data as $data){
$prime_update_val = '';
$column_name = explode(',', $data['prime_column_val'] ?? "");
$cell_value = explode(',', $data['prime_cell_val'] ?? "");
foreach($column_name as $index => $col_name){
if(isset($cell_value[$index])){
$col_value = $cell_value[$index];
$prime_update_val .= $col_name .'='.''.$col_value.',';
}
}
$prime_update_val .= 'trans_updated_by = "'.$this->logged_id.'", trans_updated_date = "'.$created_on.'"';
$update_query = 'UPDATE '.$this->prime_table.' SET '.$prime_update_val.' WHERE prime_transactions_fms_id="'.$data['prime_id'].'"';
$this->db->query($update_query);
}
echo json_encode(array('success' => true,'message' => "Successfully file imported"));
}
//GENERATING ARRAY FOR PICKLIST
public function get_pick_list_data($pick_column_rslt){
$pick_list_data = array();
foreach($pick_column_rslt as $setting){
$label_id = $setting->label_name;
$field_type = (int)$setting->field_type;
$pick_list_type = (int)$setting->pick_list_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$module_id = $setting->prime_module_id;
$pick_list_import = (int)$setting->pick_list_import;
//FOR PICKLIST
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1 ){
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query = 'SELECT '.$pick_list.' FROM '.$pick_table.' ';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$prime_id = array_map(function($val) use($pick_list_val_1){
return $val->$pick_list_val_1;
}, $pick_result);
$prime_val = [];
foreach ($pick_result as $val) {
$prime_val[$val->$pick_list_val_1] = $val->$pick_list_val_2;
}
$pick_list_data[] = [$module_id => [$label_id => [$pick_list_val_1 => $prime_id,$pick_list_val_2 => $prime_val]]];
}elseif($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query = 'SELECT count(*) AS rslt_count FROM '.$pick_table.' WHERE '.$pick_list_val_2.' = "'.$col_value.'"';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
foreach($pick_result as $row){
$prime_id = $row->$pick_list_val_1;
$prime_val = $row->$pick_list_val_2;
// $pick_list_data[$module_id] = [$pick_list_type => [$label_id => [$pick_list_val_1 => $prime_id,$pick_list_val_2 => $prime_val]]];
$pick_list_data[] = [$module_id => [$label_id => [$pick_list_val_1 => $prime_id,$pick_list_val_2 => $prime_val]]];
}
}
}
}
return $pick_list_data;
}
}
?>