File: /home/cafsindia/uds.cafsinfotech.in/application/controllers_bk/Pay_structure.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
require('./application/libraries/phpspreadsheet/autoload.php');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Pay_structure extends Action_controller{
public function __construct(){
parent::__construct('pay_structure');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['quick_link'] = $this->quick_link;
$category_rslt = $this->query_build_function('*','cw_category','',' cw_category.trans_status = 1 and cw_category.prime_category_id != 1');
$category_list = "<option value = ''>---- Category List ----</option>";
$category_arr = array();
foreach($category_rslt as $for){
$prime_id = $for['prime_category_id'];
$category_name = $for['category_name'];
$category_list .= "<option value = '$prime_id'>$category_name</option>";
$category_arr[$category_name] = $prime_id;
}
$data['category_list'] = $category_list;
$data['category_arr'] = $category_arr;
//select query for get a day condition
$day_cond_rslt = $this->query_build_function('cw_day_condition.prime_day_condition_id,cw_day_condition.day_condition','cw_day_condition','','cw_day_condition.trans_status = 1');
$day_cond_list = "<option value = ''>---- Day Condition List ----</option>";
$day_cond_arr = array();
foreach($day_cond_rslt as $for){
$prime_day_id = $for['prime_day_condition_id'];
$day_condition = $for['day_condition'];
$day_cond_list .= "<option value = '$prime_day_id'>$day_condition</option>";
$day_cond_arr[$day_condition] = $prime_day_id;
}
$data['day_cond_list'] = $day_cond_list;
$data['day_cond_arr'] = $day_cond_arr;
//select query for get a day count
$day_count_rslt = $this->query_build_function('cw_days.day_id,cw_days.day','cw_days','','cw_days.trans_status = 1');
$day_count_list = "<option value = ''>---- Day Count List ----</option>";
$day_count_arr = array();
foreach($day_count_rslt as $for){
$prime_day_id = $for['day_id'];
$day = $for['day'];
$day_count_list .= "<option value = '$prime_day_id'>$day</option>";
$day_count_arr[$day] = $prime_day_id;
}
$data['day_count_list'] = $day_count_list;
$data['day_count_arr'] = $day_count_arr;
// //select query for get ptax details
// $pro_tax_rslt = $this->query_build_function('cw_sap_professional_tax.ptax_code as ptax_code,cw_sap_professional_tax.ptax_name','cw_sap_professional_tax','','cw_sap_professional_tax.trans_status = 1');
// $pro_tax_list = "<option value = ''>---- Professional Tax List ----</option>";
// $pro_tax_arr = array();
// foreach($pro_tax_rslt as $for){
// $ptax_code = $for['ptax_code'];
// $ptax_name = $for['ptax_name'];
// $pro_tax_list .= "<option value = '$ptax_code'>$ptax_name</option>";
// $pro_tax_arr[$ptax_name] = $ptax_code;
// }
// $data['pro_tax_list'] = $pro_tax_list;
// $data['pro_tax_arr'] = $pro_tax_arr;
$status_rslt = $this->query_build_function('*','cw_status_mode','',' cw_status_mode.trans_status = 1');
$status_list = "";
$status_arr = array();
foreach($status_rslt as $for){
$prime_id = $for['prime_status_mode_id'];
$status_mode = $for['status_mode_value'];
$status_list .= "<option value = '$prime_id'>$status_mode</option>";
$status_arr[$status_mode] = $prime_id;
}
$data['status_list'] = $status_list;
$data['status_arr'] = $status_arr;
//EMPLOYEE TYPE PICKLIST AND ARRAY
$emp_type_list = "<option value = ''>----Select Status ----</option><option value = '1'>Internal</option><option value = '2'>External</option>";
$data['emp_type_list'] = $emp_type_list;
$data['emp_type_arr'] = array(""=>"---- Employee Type ----","Internal" => 1,"External" => 2);
//select query for get personal area details
$per_area_rslt = $this->query_build_function('personal_code,personal_name','cw_sap_personal_area','','trans_status = 1 and FIND_IN_SET(personal_code, "'.$this->logged_area_access.'") ');
//Generate list for Datalist
$per_area_list = "";
foreach($per_area_rslt as $for){
$personal_code = $for['personal_code'];
$personal_name = $for['personal_name'];
if($personal_code !== ""){
$per_area_list .= "<option data-value='".$personal_code."' value='".trim($personal_code)."' >".trim($personal_name)."</option>";
}
}
$data['per_area_list'] = $per_area_list;
$data['encKey'] = $this->generateKey();
//FOR GET PAY STRUCTURE DATA BASED ON PAY STRUCTURE FILTER SETTINGS
$data['pay_add_filter_setting'] = $this->pay_add_filter_col_setting();
$this->load->view("$this->control_name/manage",$data);
}
//FUNCTION FOR GET DYNAMICAL PAY STRUCTURE ADD COLUMN ARRAY FROM PAY ADD AND FILTER STRUCTURE SETTINGS
public function pay_add_filter_pick_column($type){
$pay_add_filter_column_rslt = $this->pay_add_filter_col_setting();
$process = "filter";
$pay_add_filter_arr = $this->pay_picklist_get_function($process,$pay_add_filter_column_rslt,$type);
return $pay_add_filter_arr;
}
//FUNCTION FOR GET A PAY STRUCTURE DYNAMIC IMPORT CHECK COLUMN
public function pay_import_pick_column(){
$pay_import_check_col_rslt = $this->pay_import_col_setting();
$process = "import";
$type = '';
return $pay_import_arr = $this->pay_picklist_get_function($process,$pay_import_check_col_rslt,$type);
}
//MI EXIST CHECK FUNCTION
public function exist_arr($table_where_qry){
$exist_arr_check_qry = 'SELECT prime_pay_structure_id AS prime_id,GROUP_CONCAT(distinct check_status)AS check_status,GROUP_CONCAT(distinct entry_status)AS entry_status from cw_monthly_input_fms inner join cw_pay_structure on (cw_pay_structure.category = cw_monthly_input_fms.role and cw_pay_structure.personal_code = cw_monthly_input_fms.personal_code and cw_pay_structure.wbs_element = cw_monthly_input_fms.wbs_element and cw_pay_structure.position = cw_monthly_input_fms.position) where date_format(str_to_date(CONCAT("01-",cw_monthly_input_fms.process_month), "%d-%m-%Y") , "%Y-%m") >= date_format(cw_pay_structure.from_date, "%Y-%m") and date_format(str_to_date(CONCAT("01-",cw_monthly_input_fms.process_month), "%d-%m-%Y") , "%Y-%m") <= date_format(cw_pay_structure.to_date, "%Y-%m") and cw_monthly_input_fms.trans_status = 1 and cw_monthly_input_fms.entry_status != 0 GROUP by prime_pay_structure_id';
$exist_arr_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_arr_check_qry')");
$exist_arr_rslt = $exist_arr_info->result_array();
$exist_arr_info->next_result();
if($exist_arr_rslt[0]){
$exist_arr = array();
foreach ($exist_arr_rslt as $key => $value) {
$exist_arr[$value['prime_id']] = $value;
}
}
return $exist_arr;
}
# For arrear -> view and edit btn.
public function arrear_trans(){
$arr_check_qry = 'SELECT cw_arr_info.arr_pay as prime_id,cw_arr_transactions.entry_status from cw_arr_info JOIN cw_arr_transactions ON cw_arr_info.prime_arr_info_id = cw_arr_transactions.prime_arr_info_id WHERE cw_arr_info.trans_status = 1 AND cw_arr_transactions.trans_status = 1 and cw_arr_transactions.entry_status = 1';
$arr_info = $this->db->query("CALL sp_a_run ('SELECT','$arr_check_qry')");
$arr_info_rslt = $arr_info->result_array();
$arr_info->next_result();
if($arr_info_rslt[0]){
$arr_rslt = array();
foreach ($arr_info_rslt as $key => $value){
$arr_rslt[$value['prime_id']] = $value;
}
}
return $arr_rslt;
}
# For arrear -> delete btn.
public function arrear_info(){
$arr_check_qry = 'SELECT cw_arr_info.arr_pay as prime_id from cw_arr_info WHERE trans_status = 1 ';
$arr_info = $this->db->query("CALL sp_a_run ('SELECT','$arr_check_qry')");
$arr_info_rslt = $arr_info->result_array();
$arr_info->next_result();
if($arr_info_rslt[0]){
$arr_rslt = array();
foreach ($arr_info_rslt as $key => $value){
$arr_rslt[$value['prime_id']] = $value;
}
}
return $arr_rslt;
}
//PYROLL PROCESS EXIST CHECK BASED ON CATEGORY AND FROM DATE
public function payroll_process_check(){
$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);
}
$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");
$personal_code = $this->input->post("personal_code");
$wbs_element = $this->input->post("wbs_element");
$position = $this->input->post("position");
$activity_no = $this->input->post("activity_no");
$network_id = $this->input->post("network_id");
$from_date = $this->input->post("from_date");
$prime_id = $this->input->post("prime_id");
$exist_count = 0;
$exist_id = $this->pay_struct_exist_fun_con($prime_id,$category,$from_date,"add");
if($exist_id){
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Pay Structure Already Exist for this Components! Please Check Your Apply Date..!"));
}else{
echo json_encode(array('success' => true, 'message' => "Proceed..!"));
}
}
//FUNCTION FOR CHECK A MI AND PAYROLL EXIST BASED ON CATEGORY AND PROCESS MONTH
public function payroll_exist_check_qry($category,$from_date,$personal_code,$wbs_element,$position){
//FRIST WE CHECK MI FMS TABLE
$mi_ext_check_qry = 'select count(prime_monthly_input_fms_id) as count from cw_monthly_input_fms where cw_monthly_input_fms.role = "'.$category.'" and cw_monthly_input_fms.personal_code = "'.$personal_code.'" and cw_monthly_input_fms.wbs_element = "'.$wbs_element.'" and cw_monthly_input_fms.position = "'.$position.'" and date_format(str_to_date(CONCAT("01-",cw_monthly_input_fms.process_month), "%d-%m-%Y") , "%Y-%m") >= date_format("'.$from_date.'", "%Y-%m") and cw_monthly_input_fms.trans_status = 1 and cw_monthly_input_fms.check_status in (1)';
// and cw_monthly_input_fms.entry_status in (1,2)
$mi_ext_check_info = $this->db->query("CALL sp_a_run ('SELECT','$mi_ext_check_qry')");
$mi_ext_check_rslt = $mi_ext_check_info->result();
$mi_ext_check_info->next_result();
return $mi_ext_check_rslt;
// -------- DON'T DELETE -------------
/*if((int)$mi_ext_check_rslt[0]->count){
return $mi_ext_check_rslt;
}else{//SECOND WE CHECK A PAYROLL FMS TABLE
$payroll_ext_check_qry = 'select count(prime_transactions_fms_id) as count from cw_transactions_fms where cw_transactions_fms.trans_status = 1 and cw_transactions_fms.role = "'.$category.'" and cw_transactions_fms.personal_code = "'.$personal_code.'" and cw_transactions_fms.wbs_element = "'.$wbs_element.'" and cw_transactions_fms.position = "'.$position.'" and date_format(str_to_date(CONCAT("01-",cw_transactions_fms.process_month), "%d-%m-%Y") , "%Y-%m") >= date_format("'.$from_date.'", "%Y-%m")';
$payroll_ext_check_info = $this->db->query("CALL sp_a_run ('SELECT','$payroll_ext_check_qry')");
$payroll_ext_check_rslt = $payroll_ext_check_info->result();
$payroll_ext_check_info->next_result();
return $payroll_ext_check_rslt;
}*/
// -------- DON'T DELETE -------------
}
//CHECK A PAY STRUCTURE EXIST FUNCTION
public function pay_struct_exist_fun_con($prime_id,$category,$from_date,$type){
$pay_filter_setting = $this->pay_add_filter_col_setting();
$table_where_qry = "";
$ins_qry_key = "";
$ins_qry_val = "";
$upd_qry_key_val = "";
//PAY STRUCTURE EXIST NOT CHECK COLUMN ARRAY
$not_exist_check_col = array("activity_no","network_id");
foreach($pay_filter_setting as $setting){
$label_name = $setting->label_name;
$label_id = $this->input->post("$label_name");
if($label_name === "role"){
$label_name = "category";
}
if($label_id && ($label_name !== "role" || $label_name !== "category") && !in_array($label_name,$not_exist_check_col)){
$table_where_qry .= $this->prime_table.'.'.$label_name.' = "' . $label_id . '" and ';
}
}
//table where qry exist select function
$select_columns = 'cw_pay_structure.prime_pay_structure_id as prime_id';
$table_name = 'cw_pay_structure';
$table_join = '';
//DR CODE START FOR COPY AND SAVE PAY STRUCUTURE PRIME ID DIFFERNECE WHERE CONDITION CHECK
$id_from_date_whre = '';
if($type === "save" || $type === "copy" || $type === "add"){
// (cw_pay_structure.from_date > "'.$from_date.'" or (
//ON(09-09-2023) DISCUSSED WITH PORUL
//$id_from_date_whre = ' and cw_pay_structure.from_date >= "'.$from_date.'" and cw_pay_structure.prime_pay_structure_id != "'.$prime_id.'"';
if($type !== "save"){
$payroll_exist_rslt = $this->payroll_exist_check_qry($category,$from_date,$personal_code,$wbs_element,$position);
if((int)$payroll_exist_rslt[0]->count){
$exist_count = (int)$payroll_exist_rslt[0]->count;
}
if($exist_count){
echo json_encode(array('success' => false, 'message' => "Pre Audit Completed for this month.. Please remove the Pre Audit and try again..!"));
exit(0);
}
}
$id_from_date_whre = 'and cw_pay_structure.from_date = "'.$from_date.'" and cw_pay_structure.prime_pay_structure_id != "'.$prime_id.'"';
}
//DR CODE START FOR COPY AND SAVE PAY STRUCUTURE PRIME ID DIFFERNECE WHERE CONDITION CHECK
$table_where = $table_where_qry.' cw_pay_structure.trans_status = 1 '.$id_from_date_whre.'';
$exist_pay_struct_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$prime_id = (int)$exist_pay_struct_rslt[0]['prime_id'];
return $prime_id;
}
//PAY STRUCTURE SHOW FUNCTION
public function show_pay_structure(){
$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");
$prime_id = 0;
$pf_exempt = 2;
$esi_exempt = 2;
$lwf_exempt = 2;
//FUNCTION FOR CREATE A EARNINGS AND DEDUCTION TABLE FORMAT
$ear_ded_tab_info = $this->pay_struct_line_table_create($prime_id,$category,$pf_exempt,$esi_exempt,$lwf_exempt);
// ,cw_pay_structure_line.field_type
if(!empty($ear_ded_tab_info)){
echo json_encode(array('success' => true, 'ear_table_rslt' => $ear_ded_tab_info['earnings'], 'ded_table_rslt' => $ear_ded_tab_info['deduction'],'drop_select_arr' => $ear_ded_tab_info['drop_select'],"prime_id" => $prime_id, 'message' => "Pay Structure Earnings and Deduction Details..!"));
}else{
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Payroll Formula or Paystrcuture check Process not Added for this Category..!"));
}
}
//PAY STRUCTURE INPUT SETTINGS PICKLIST VALUE FETCH(IF WE CLICK A EDIT BUTTON)
public function pay_struct_search_drop_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);
}
$prime_id = (int)$this->input->post('prime_id');
//FOR GET PAY STRUCTURE FILTER DETAILS BASED ON PAY STRUCTURE FILTER SETTINGS
$pay_filter_setting = $this->pay_add_filter_col_setting();
$select_label_qry = "";
foreach($pay_filter_setting as $setting){
$label_name = $setting->label_name;
if($label_name){
$select_label_qry .= ',cw_pay_structure.'.$label_name;
}
}
$pay_struct_qry = 'select cw_pay_structure.prime_pay_structure_id as prime_id'.$select_label_qry.' from cw_pay_structure where cw_pay_structure.prime_pay_structure_id = "'.$prime_id.'" and cw_pay_structure.trans_status = 1';
$pay_struct_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_struct_qry')");
$pay_struct_result = $pay_struct_info->result_array();
$pay_struct_info->next_result();
//DR CODE START 25AUG22 FOR WBS BASED SAP ACTIVITY PICKLIST GET
//activity no fetch
$wbs_element = $pay_struct_result[0]['wbs_element'];
$sap_act_no_qry = 'select prime_sap_activity_id,act_no,act_desc,act_wbs_id from cw_sap_activity inner join cw_sap_wbs on cw_sap_wbs.wbs_id = cw_sap_activity.act_wbs_id where cw_sap_wbs.wbs_id = "'.$wbs_element.'" and cw_sap_activity.trans_status = 1 and cw_sap_wbs.trans_status = 1';
$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();
$sap_act_no_list = "<option value = ''>---- Select Activity ----</option>";
foreach($sap_act_no_rslt as $for){
$prime_id = $for['prime_sap_activity_id'];
$act_no = $for['act_no'];
$act_desc = $for['act_desc'];
$sap_act_no_list .= "<option data-value='".$prime_id."' value='".trim($act_desc)."' >".trim($act_no)."</option>";
}
//DR CODE END 25AUG22 FOR WBS BASED SAP ACTIVITY PICKLIST GET
if($pay_struct_result[0]){
echo json_encode(array('success' => true, 'input_rslt' => $pay_struct_result[0], 'sap_act_no_list' => $sap_act_no_list));
}else{
echo json_encode(array('success' => false, 'input_rslt' => ''));
}
}
//GET LAST PAY STRUCTURE DATA BASED ON PAY STRUCTURE SETTINGS AND CATEGORY AND TA LOCATION
public function get_last_pay_struct_data($prime_id,$table_where_qry,$category,$tax_location,$type){
$select_columns = 'prime_pay_structure_id as prime_id,cw_category.category_name as category,cw_pay_structure.from_date,cw_pay_structure.to_date,cw_pay_structure.effective_date';
$table_name = 'cw_pay_structure';
$table_join = 'inner join cw_category on cw_category.prime_category_id = cw_pay_structure.category';
//DR CODE START FOR COPY AND SAVE PAY STRUCUTURE PRIME ID DIFFERNECE WHERE CONDITION CHECK
if($type === "save"){
$prime_id_whre = ' and cw_pay_structure.prime_pay_structure_id != "'.$prime_id.'"';
}else
if($type === "copy"){
$prime_id_whre = '';
}
//DR CODE START FOR COPY AND SAVE PAY STRUCUTURE PRIME ID DIFFERNECE WHERE CONDITION CHECK
$table_where = $table_where_qry.' cw_pay_structure.category = "'.$category.'" and cw_pay_structure.trans_status = 1 '.$prime_id_whre.' ORDER BY prime_pay_structure_id DESC LIMIT 0,1';
$last_pay_struct_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
return $last_pay_struct_rslt;
}
// pay structure table select query
public function pay_struct_select_fun($table_name,$prime_pay_struct_id){
$select_columns = '*';
$table_join = '';
$table_where = $table_name.'.prime_pay_structure_id = "'.$prime_pay_struct_id.'" and '.$table_name.'.trans_status = 1';
$pay_line_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
return $pay_line_rslt;
}
//PAY STRUCTURE SEARCH FUNCTION
public function search_pay_structure(){
$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);
}
//FOR GET PAY STRUCTURE FILTER DETAILS BASED ON PAY STRUCTURE FILTER SETTINGS
$pay_filter_setting = $this->pay_add_filter_col_setting();
$table_where_qry = "";
$select_label_qry = "";
$search = $this->input->post('search');
// $search = trim($search['value']);
$common_search = "";
foreach($pay_filter_setting as $setting){
$label_name = $setting->label_name;
$label_id = $this->input->post("$label_name");
$pick_list = $setting->pick_list;
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($label_name === "role"){
$label_name = "category";
}
$mandatory_col = (int)$setting->mandatory_column;
if($label_id){
$table_where_qry .= 'cw_pay_structure.'.$label_name.' = "' . $label_id . '" and ';
if($search){
$common_search .= 'cw_pay_structure.'.$label_name.' like "'.$label_id.'%" or ';
}
}
if($label_name !== "category" && $label_name !== "personal_code" && $label_name !== "network_id"){
$select_label_qry .= ',cw_pay_structure.'.$label_name;
}
}
//FOR DATATABLE SEARCH QRY
if($common_search){
$common_search = ' and ('.$common_search;
$common_search .= ' cw_pay_structure.tax_location like "'.$search.'%" or cw_pay_structure.employee_type like "'.$search.'%" or cw_pay_structure.from_date like "'.date('Y-m-d',strtotime($search)).'%" or cw_pay_structure.to_date like "'.date('Y-m-d',strtotime($search)).'%" or cw_pay_structure.effective_date like "'.date('Y-m-d',strtotime($search)).'%" or cw_pay_structure.day_condition like "'.$search.'%")';
}
//select query for show a pay structure details
$select_columns = 'prime_pay_structure_id as prime_id,cw_category.category_name as category,cw_pay_structure.category as category_id,cw_sap_position.position_name as position_name,cw_sap_activity.act_no as activity,cw_sap_professional_tax.ptax_name as tax_location,cw_pay_structure.employee_type,cw_pay_structure.from_date,cw_pay_structure.to_date,cw_pay_structure.effective_date,cw_day_condition.day_condition as day_condition,cw_pay_structure.day_count,status1.status_mode_value as pf_limit,cw_pay_structure.gross,cw_pay_structure.pf_gross,cw_pay_structure.esi_gross,cw_pay_structure.status'.$select_label_qry;
$table_name = 'cw_pay_structure';
$table_join = 'inner join cw_category on cw_category.prime_category_id = cw_pay_structure.category inner join cw_sap_position on cw_sap_position.position_code = cw_pay_structure.position inner join cw_sap_activity on cw_sap_activity.prime_sap_activity_id = cw_pay_structure.activity_no inner join cw_sap_professional_tax on cw_sap_professional_tax.ptax_code = cw_pay_structure.tax_location inner join cw_day_condition on cw_day_condition.prime_day_condition_id = cw_pay_structure.day_condition inner join cw_status_mode status1 on status1.prime_status_mode_id = cw_pay_structure.pf_limit';
$table_where = $table_where_qry.' cw_pay_structure.trans_status = 1 and cw_day_condition.trans_status = 1 ORDER BY prime_pay_structure_id DESC';
// '.$common_search.'
$search_pay_struct_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
if($search_pay_struct_rslt[0]){
$prime_id = $search_pay_struct_rslt[0]['prime_id'];
$category = $search_pay_struct_rslt[0]['category_id'];
//---------------- PLEASE DONT REMOVE THIS CODE ----------------------------------------------
//FUNCTION FOR CREATE A EARNINGS AND DEDUCTION TABLE FORMAT
// $ear_ded_tab_info = $this->pay_struct_line_table_create($prime_id,$category,$pf_exempt,$esi_exempt,$lwf_exempt);
// // ,cw_pay_structure_line.field_type
// if(!empty($ear_ded_tab_info)){
// echo json_encode(array('success' => true, 'table_rslt' => $search_pay_struct_rslt, 'ear_table_rslt' => $ear_ded_tab_info['earnings'], 'ded_table_rslt' => $ear_ded_tab_info['deduction'],'drop_select_arr' => $ear_ded_tab_info['drop_select'],"prime_id" => $prime_id, 'message' => "Pay Structure Details are Display Below..!"));
// }else{
# Disable edit and show view when arrear submitted from branch user.
$arr_trans = $this->arrear_trans();
# Disable delete btn if pay st has any arrear entry.
$arr_info = $this->arrear_info();
//Get exist record from CATS Entry
$exist_arr = $this->exist_arr($table_where_qry);
echo json_encode(array('success' => true, 'table_rslt' => $search_pay_struct_rslt, 'message' => "Pay Structure History.!",'exist_arr'=>$exist_arr,'arr_trans'=>$arr_trans,'arr_info'=>$arr_info));
// }
//--------------------------------------------------------------------------------------------------
}else{
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "No Records Found..!"));
}
}
//PAY STRUCTURE COPY FUNCTION 22AUG22
public function copy_pay_structure(){
$prime_id = (int)$this->input->post('prime_id');
$category = $this->input->post('category');
$tax_location = $this->input->post('tax_location');
$from_date = date("Y-m-d",strtotime($this->input->post('from_date')));
$effective_date = $this->input->post('effective_date');
if($effective_date){
$effective_date = date("Y-m-d",strtotime($effective_date));
}else{
$effective_date = $from_date;
}
$personal_code = $this->input->post("personal_code");
$wbs_element = $this->input->post("wbs_element");
$position = $this->input->post("position");
$logged_id = $this->logged_id;
$date = date("Y-m-d H:i:s");
//FOR GET PAY STRUCTURE FILTER DETAILS AND QRY BASED ON PAY STRUCTURE FILTER SETTINGS
$pay_filter_setting = $this->pay_add_filter_col_setting();
$table_where_qry = "";
$ins_qry_key = "";
$ins_qry_val = "";
$upd_qry_key_val = "";
//PAY STRUCTURE EXIST NOT CHECK COLUMN ARRAY
$not_exist_check_col = array("activity_no","network_id");
foreach($pay_filter_setting as $setting){
$label_name = $setting->label_name;
$label_id = $this->input->post("$label_name");
if($label_name === "role"){
$label_name = "category";
}
if($label_id){
if(!in_array($label_name,$not_exist_check_col)){
$table_where_qry .= $this->prime_table.'.'.$label_name.' = "'.$label_id.'" and ';
}
$ins_qry_key .= $label_name.',';
$ins_qry_val .= $label_id.'","';
}
}
// $table_where_qry = rtrim($table_where_qry," and");
//PAYROLL EXIST CHECK FOR THIS COMPONENTS AND DATE
$payroll_exist_rslt = $this->payroll_exist_check_qry($category,$from_date,$personal_code,$wbs_element,$position);
$exist_count = (int)$payroll_exist_rslt[0]->count;
if($exist_count){
echo json_encode(array('success' => false, 'message' => "Pre Audit Completed for this Month..!"));
exit(0);
}else{
// $tax_location,
$exist_id = $this->pay_struct_exist_fun_con($prime_id,$category,$from_date,"copy");
if($exist_id){
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Pay Structure Already Exist for this Components! Please Check Your Apply Date..!"));
exit(0);
}else{
$last_pay_struct_rslt = $this->get_last_pay_struct_data($prime_id,$table_where_qry,$category,$tax_location,"copy");
//same pay structure but from and to date should change from same components in last pay structure data
if($last_pay_struct_rslt[0]){
$last_prime_id = (int)$last_pay_struct_rslt[0]['prime_id'];
$last_to_date = date("Y-m-d",strtotime("-1 days",strtotime($from_date)));
//INSERT INTO SELECT QRY FOR COPY A DATA TO INSERT BY A SAME TABLE
$ins_pay_struct_qry = 'INSERT INTO cw_pay_structure(cw_pay_structure.personal_code,cw_pay_structure.wbs_element,cw_pay_structure.position,cw_pay_structure.activity_no,cw_pay_structure.network_id,cw_pay_structure.category,cw_pay_structure.tax_location,cw_pay_structure.employee_type,cw_pay_structure.from_date,cw_pay_structure.to_date,cw_pay_structure.effective_date,cw_pay_structure.day_condition,cw_pay_structure.day_count,cw_pay_structure.pf_limit,cw_pay_structure.esi_limit,cw_pay_structure.pf_exempt,cw_pay_structure.esi_exempt,cw_pay_structure.lwf_exempt,cw_pay_structure.ear_gross,cw_pay_structure.pf_gross,cw_pay_structure.esi_gross,cw_pay_structure.gross,cw_pay_structure.status,cw_pay_structure.trans_created_by,cw_pay_structure.trans_created_date) SELECT a.personal_code,a.wbs_element,a.position,a.activity_no,a.network_id,a.category,a.tax_location,a.employee_type,"'.$from_date.'","2099-12-31","'.$effective_date.'",a.day_condition,a.day_count,a.pf_limit,a.esi_limit,a.pf_exempt,a.esi_exempt,a.lwf_exempt,a.ear_gross,a.gross,"1","'.$logged_id.'","'.$date.'" FROM cw_pay_structure AS a WHERE a.trans_status = 1 and a.prime_pay_structure_id = "'.$prime_id.'"';
$ins_pay_struct_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_pay_struct_qry')");
$ins_pay_struct_rslt = $ins_pay_struct_info->result();
$ins_pay_struct_info->next_result();
$ins_prime_id = (int)$ins_pay_struct_rslt[0]->ins_id;
//TO INSERT A PAY STRUCTURE LINE TABLE
if($ins_prime_id){
$ins_pay_line_qry = 'INSERT INTO cw_pay_structure_line(cw_pay_structure_line.prime_pay_structure_id,cw_pay_structure_line.field_type,cw_pay_structure_line.wage_code,cw_pay_structure_line.wage_name,cw_pay_structure_line.hrms_field_name,cw_pay_structure_line.amount,cw_pay_structure_line.pf_applicable,cw_pay_structure_line.esi_applicable,cw_pay_structure_line.pt_applicable,cw_pay_structure_line.trans_created_by,cw_pay_structure_line.trans_created_date) SELECT "'.$ins_prime_id.'",a.field_type,a.wage_code,a.wage_name,a.hrms_field_name,a.amount,a.pf_applicable,a.esi_applicable,a.pt_applicable,"'.$logged_id.'","'.$date.'" FROM cw_pay_structure_line AS a WHERE a.trans_status = 1 and a.prime_pay_structure_id = "'.$prime_id.'"';
$ins_pay_line_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_pay_line_qry')");
$ins_pay_line_rslt = $ins_pay_struct_info->result();
$ins_pay_line_info->next_result();
$ins_prime_id = (int)$ins_pay_line_rslt[0]->ins_id;
//update qry for to date should update to last pay structure row data
$upd_last_pay_struct_qry = 'UPDATE cw_pay_structure SET cw_pay_structure.to_date = "'.$last_to_date.'",cw_pay_structure.status = "2",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" WHERE cw_pay_structure.prime_pay_structure_id = "'.$last_prime_id.'" and cw_pay_structure.trans_status = 1';
$upd_last_pay_struct_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_last_pay_struct_qry')");
echo json_encode(array('success' => true, 'message' => "Pay Structure Successfully Added..!"));
}else{
echo json_encode(array('success' => false, 'message' => "Please Reset Your Process Once Again..!"));
}
}else{
echo json_encode(array('success' => false, 'message' => "Please Verify Your Pay structure Components..!"));
}
}
}
}
// ----------------- PAY STRUCTURE DELETE PROCESS START -----------------
public function delete_pay_structure(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$prime_id = (int)$this->input->post('prime_id');
$category = $this->input->post('category');
$from_date = date("Y-m-d",strtotime($this->input->post('from_date')));
$to_date = date("Y-m-d",strtotime($this->input->post('to_date')));
$logged_id = $this->logged_id;
$date = date("Y-m-d H:i:s");
$sts = TRUE;
// ARREAR PROCESSED PAY ST SHOULD NOT ALLOW TO DELETE.
$arr_qry = 'SELECT * FROM cw_arr_info WHERE arr_pay = "'.$prime_id.'" AND trans_status = 1 ';
$arr_info = $this->db->query("CALL sp_a_run('SELECT','$arr_qry')");
$arr_rslt = $arr_info->result();
$arr_info->next_result();
if(count($arr_rslt) > 0){
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Arrear Entry Processed for this Pay Structure.. Please remove the Arrear Entry and try again..!"));
exit(0);
}
//PAY STRUCTURE SELECT QUERY FOR GET A FILTER WHERE CONDITIONS QRY COLUMNS DATA
$pay_struct_qry = 'select * from cw_pay_structure WHERE cw_pay_structure.prime_pay_structure_id = "'.$prime_id.'" and cw_pay_structure.trans_status = 1';
$pay_struct_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_struct_qry')");
$pay_struct_rslt = $pay_struct_info->result_array();
$pay_struct_info->next_result();
//GET DELETE QRY
if(count($pay_struct_rslt)){
//FOR GET PAY STRUCTURE FILTER DETAILS AND QRY BASED ON PAY STRUCTURE FILTER SETTINGS
$pay_filter_setting = $this->pay_add_filter_col_setting();
$table_where_qry = "";
//PAY STRUCTURE EXIST NOT CHECK COLUMN ARRAY
$not_exist_check_col = array("activity_no","network_id");
foreach($pay_filter_setting as $setting){
$label_name = $setting->label_name;
$label_id = $pay_struct_rslt[0][$label_name];
if($label_name === "role"){
$label_name = "category";
}
if($label_id){
if(!in_array($label_name,$not_exist_check_col)){
$table_where_qry .= $this->prime_table.'.'.$label_name.' = "' . $label_id . '" and ';
}
}
}
$personal_code = $pay_struct_rslt[0]['personal_code'];
$wbs_element = $pay_struct_rslt[0]['wbs_element'];
$position = $pay_struct_rslt[0]['position'];
$tax_location = $pay_struct_rslt[0]['tax_location'];
$activity_no = $pay_struct_rslt[0]['activity_no'];
$network_id = $pay_struct_rslt[0]['network_id'];
//Check Monthly Input already processed or not
$mi_ext_check_qry = 'select count(prime_monthly_input_fms_id) as count from cw_monthly_input_fms where cw_monthly_input_fms.role = "'.$category.'" and cw_monthly_input_fms.personal_code = "'.$personal_code.'" and cw_monthly_input_fms.wbs_element = "'.$wbs_element.'" and cw_monthly_input_fms.position = "'.$position.'" and date_format(str_to_date(CONCAT("01-",cw_monthly_input_fms.process_month), "%d-%m-%Y") , "%Y-%m") >= date_format("'.$from_date.'", "%Y-%m") and date_format(str_to_date(CONCAT("01-",cw_monthly_input_fms.process_month), "%d-%m-%Y") , "%Y-%m") <= date_format("'.$to_date.'", "%Y-%m") and cw_monthly_input_fms.trans_status = 1 and cw_monthly_input_fms.entry_status in (1,2)';
$mi_ext_check_info = $this->db->query("CALL sp_a_run ('SELECT','$mi_ext_check_qry')");
$mi_ext_check_rslt = $mi_ext_check_info->result();
$mi_ext_check_info->next_result();
if((int)$mi_ext_check_rslt[0]->count > 0){
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "CATS Entry Processed for this month.. Please remove the CATS Entry and try again..!"));
exit(0);
}
// AND activity_no = "'.$activity_no.'" AND network_id ="'.$network_id.'" -> prd pt => 23
// AND category = "'.$category.'" -> prd pt => 59
$last_pay_structure_qry = 'SELECT prime_pay_structure_id,to_date FROM cw_pay_structure WHERE to_date = ( SELECT MAX(to_date) FROM cw_pay_structure WHERE personal_code ="'.$personal_code.'" AND wbs_element ="'.$wbs_element.'" AND POSITION = "'.$position.'" AND to_date < "'.$to_date.'" AND trans_status = 1) and personal_code ="'.$personal_code.'" AND wbs_element ="'.$wbs_element.'" AND POSITION = "'.$position.'" AND to_date < "'.$to_date.'" AND trans_status = 1';
$last_pay_struc_info = $this->db->query("CALL sp_a_run ('SELECT','$last_pay_structure_qry')");
$last_pay_struc_rslt = $last_pay_struc_info->result();
$last_pay_struc_info->next_result();
$last_prime_id = $last_pay_struc_rslt[0]->prime_pay_structure_id;
//LAST INACTIVE PAY STRUCTURE TO UPDATE TO ACTIVE
if($last_prime_id){
if($last_prime_id && $prime_id){
//update qry for to date should update to last pay structure row data
$upd_last_pay_struct_qry = 'UPDATE cw_pay_structure SET to_date = "'.$to_date.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" WHERE cw_pay_structure.prime_pay_structure_id = "'.$last_prime_id.'" and cw_pay_structure.trans_status = 1';
$upd_last_pay_struct_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_last_pay_struct_qry')");
}else{
$sts = FALSE;
}
}
if($sts){
//CURRENT PAY STRUCTURE AND PAY STRUCTURE LINE DELETE QURY
//and cw_pay_structure.status = 1
$del_pay_struct_qry = 'UPDATE cw_pay_structure SET cw_pay_structure.status = "2",trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'",cw_pay_structure.trans_status = "0" WHERE cw_pay_structure.prime_pay_structure_id = "'.$prime_id.'" and cw_pay_structure.trans_status = 1';
$del_pay_struct_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$del_pay_struct_qry')");
if($del_pay_struct_rslt){
$del_pay_line_qry = 'UPDATE cw_pay_structure_line SET cw_pay_structure_line.trans_deleted_by = "'.$logged_id.'",cw_pay_structure_line.trans_deleted_date = "'.$date.'",cw_pay_structure_line.trans_status = "0" WHERE cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" and cw_pay_structure_line.trans_status = 1';
$del_pay_line_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$del_pay_line_qry')");
}else{
$sts = FALSE;
}
if($sts){
$max_to_date_qry ='UPDATE cw_pay_structure SET status = CASE WHEN to_date = (SELECT MAX(to_date) FROM (SELECT * FROM cw_pay_structure WHERE wbs_element = "'.$wbs_element.'" AND personal_code ="'.$personal_code.'" AND POSITION = "'.$position.'" AND network_id = "'.$network_id.'" and activity_no = "'.$activity_no.'" AND category = "'.$category.'" and trans_status = 1) AS ps) THEN 1 ELSE 2 END WHERE wbs_element = "'.$wbs_element.'" AND personal_code ="'.$personal_code.'" AND POSITION = "'.$position.'" AND network_id = "'.$network_id.'" and activity_no = "'.$activity_no.'" AND category = "'.$category.'" and trans_status = 1';
$upd_pay_struct_sts = $this->db->query("CALL sp_a_run ('UPDATE','$max_to_date_qry')");
echo json_encode(array('success' => TRUE, 'message' => "Pay Structure Successfully Deleted.!"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Pay Structure Delete Error.!"));
}
}
}
}
// ----------------- PAY STRUCTURE DELETE PROCESS END -------------------------------
//pay structure save function
public function save_pay_structure(){
$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);
}
$action = $this->input->post('action');
$prime_id = (int)$this->input->post('prime_id');
$category = $this->input->post('category');
$tax_location = $this->input->post('tax_location');
$from_date = date("Y-m-d",strtotime($this->input->post('from_date')));
$chk_frm_date = $from_date;
$to_date = date("Y-m-d",strtotime($this->input->post('to_date')));
$effective_date = date("Y-m-d",strtotime($this->input->post('effective_date')));
$day_condition = $this->input->post('day_condition');
$day_count = $this->input->post('day_count');
$pf_limit = $this->input->post('pf_limit');
$esi_limit = $this->input->post('esi_limit');
$employee_type = $this->input->post('employee_type');
$pf_exempt = (int)$this->input->post('pf_exempt');
$esi_exempt = (int)$this->input->post('esi_exempt');
$lwf_exempt = (int)$this->input->post('lwf_exempt');
$logged_id = $this->logged_id;
$date = date("Y-m-d H:i:s");
//FOR PAY STRUCTURE LINE RELATED DATA
$json_ear_arr = json_decode($this->input->post('json_ear_obj'),true);
$json_ded_arr = json_decode($this->input->post('json_ded_obj'),true);
//FOR GET PAY STRUCTURE FILTER DETAILS AND QRY BASED ON PAY STRUCTURE FILTER SETTINGS
$pay_filter_setting = $this->pay_add_filter_col_setting();
$table_where_qry = "";
$ins_qry_key = "";
$ins_qry_val = "";
$upd_qry_key_val = "";
$wbs_element = "";
//PAY STRUCTURE EXIST NOT CHECK COLUMN ARRAY
$not_exist_check_col = array("activity_no","network_id");
foreach($pay_filter_setting as $setting){
$label_name = $setting->label_name;
$label_id = $this->input->post("$label_name");
if($label_name === "wbs_element"){
$wbs_element = $label_id;
}
if($label_name === "personal_code"){
$personal_code = $label_id;
}
if($label_name === "position"){
$position = $label_id;
}
if($label_name === "activity_no"){
$activity_no = $label_id;
}
if($label_name === "network_id"){
$network_id = $label_id;
}
if($label_name === "role"){
$label_name = "category";
}
if($label_id){
if(!in_array($label_name,$not_exist_check_col)){
$table_where_qry .= $this->prime_table.'.'.$label_name.' = "' . $label_id . '" and ';
}
$ins_qry_key .= $label_name.',';
$ins_qry_val .= $label_id.'","';
}
}
//FOR NETWORK ID
if($wbs_element){
$network_id_qry = 'select wbs_network_id from cw_sap_wbs WHERE wbs_id="'.$wbs_element.'"';
$network_info = $this->db->query("CALL sp_a_run ('SELECT','$network_id_qry')");
$network_id_rslt = $network_info->result();
$network_info->next_result();
$network_id = $network_id_rslt[0]->wbs_network_id;
$ins_qry_key .= ' network_id,';
$ins_qry_val .= $network_id.'","';
}
// $table_where_qry = rtrim($table_where_qry," and");
//PAY STRUCTURE EXIST VALIDATION CHECK BASED ON FROM DATE TO DATE AND EFFECTIVE DATE AND ALSO BASE ON FILTER COLUMN AND CATEGORY
$return_success_msg = "";
if($action !== "edit" && $action !== "view" && $action !== "copy"){
//DUPLICATE PAY STRUCTURE COMBINATION NOT ALLOWED !!!
$personal_code = $this->input->post("personal_code");
$wbs_element = $this->input->post("wbs_element");
$position = $this->input->post("position");
$activity_no = $this->input->post("activity_no");
$pfrom_date = date("Y-m",strtotime($this->input->post('from_date')));
$pto_date = date("Y-m",strtotime($this->input->post('to_date')));
//this array using to calculate a include gross amount only based on wage map hrms module
$gross_amt_arr = array_column($json_ear_arr, 'amount', 'hrms_field_name');
//INCLUDE GROSS COMPONENTS GET FOR GROSS CALCULATE
$gross_wage_map_qry = 'select cw_wage_map_hrms.wage_name,cw_wage_map_hrms.hrms_field_name from cw_wage_map_hrms where cw_wage_map_hrms.include_gross = "1" and cw_wage_map_hrms.trans_status = 1';
$gross_wage_map_info = $this->db->query("CALL sp_a_run ('SELECT','$gross_wage_map_qry')");
$gross_wage_map_rslt = $gross_wage_map_info->result_array();
$gross_wage_map_info->next_result();
$gross_wage_arr = array();
foreach ($gross_wage_map_rslt as $key => $value) {
$gross_wage_arr[$value['hrms_field_name']] = $value['hrms_field_name'];
}
$wage_gross_amt = array_sum(array_intersect_key($gross_amt_arr,$gross_wage_arr));
if((int)$wage_gross_amt === 0){
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Earning Gross should not Save to Zero.!"));
exit(0);
//--- FOR CHECK IF A ZERO EARNING AMOUNT SHOULD NOT SAVE END ---
}else{
//apply date previous pay_structure
$min_pay_struc_qry = 'select min(from_date) as min_from_date from cw_pay_structure WHERE from_date <= "'.$from_date.'" and wbs_element="'.$wbs_element.'" and personal_code="'.$personal_code.'" and position="'.$position.'" and network_id="'.$network_id.'" and trans_status=1';
$min_pay_struc_info = $this->db->query("CALL sp_a_run ('SELECT','$min_pay_struc_qry')");
$min_pay_struc_rslt = $min_pay_struc_info->result();
$min_pay_struc_info->next_result();
$min_from_date = $min_pay_struc_rslt[0]->min_from_date;
//apply date future pay_structure
$max_pay_struc_qry = 'select min(from_date) as max_from_date from cw_pay_structure WHERE from_date >= "'.$from_date.'" and wbs_element="'.$wbs_element.'" and personal_code="'.$personal_code.'" and position="'.$position.'" and network_id="'.$network_id.'" and trans_status=1';
$max_pay_struc_info = $this->db->query("CALL sp_a_run ('SELECT','$max_pay_struc_qry')");
$max_pay_struc_rslt = $max_pay_struc_info->result();
$max_pay_struc_info->next_result();
if($max_pay_struc_rslt[0]->max_from_date){
$max_from_date = date('Y-m-d', strtotime($max_pay_struc_rslt[0]->max_from_date));
$max_frm_date = date("Y-m-t",strtotime($max_from_date. ' -1 month'));
}
if($min_from_date){
$mi_rslt = $this->payroll_exist_check_qry($category,$from_date,$personal_code,$wbs_element,$position);
if((int)$mi_rslt[0]->count > 0){
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Pre Audit Completed for this month.. Please remove the Pre Audit and try again..!"));
exit(0);
}
}else{
$mi_ext_check_qry = 'select count(prime_monthly_input_fms_id) as count from cw_monthly_input_fms where cw_monthly_input_fms.role = "'.$category.'" and cw_monthly_input_fms.personal_code = "'.$personal_code.'" and cw_monthly_input_fms.wbs_element = "'.$wbs_element.'" and cw_monthly_input_fms.position = "'.$position.'" and date_format(str_to_date(CONCAT("01-",cw_monthly_input_fms.process_month), "%d-%m-%Y") , "%Y-%m") >= date_format("'.$from_date.'", "%Y-%m") and date_format(str_to_date(CONCAT("01-",cw_monthly_input_fms.process_month), "%d-%m-%Y") , "%Y-%m") <= date_format("'.$max_frm_date.'", "%Y-%m") and cw_monthly_input_fms.trans_status = 1 and cw_monthly_input_fms.check_status in (1)';
// and cw_monthly_input_fms.entry_status in (1,2)
$mi_ext_check_info = $this->db->query("CALL sp_a_run ('SELECT','$mi_ext_check_qry')");
$mi_ext_check_rslt = $mi_ext_check_info->result();
$mi_ext_check_info->next_result();
$mi_exist_count = $mi_ext_check_rslt[0]->count;
if($mi_exist_count > 0){
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Pre Audit Completed for this month.. Please remove the Pre Audit and try again..!"));
exit(0);
}
}
// ,$tax_location
$exist_id = $this->pay_struct_exist_fun_con($prime_id,$category,$from_date,"save");
if($exist_id){
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Pay Structure Already Exist for this Components! Please Check Your Apply Date..!"));
exit(0);
}else{
//GET LAST PAY STRUCTURE ID BASED ON MONTH(Changed by ragu)
//-------------------------start-----------------------------// AND network_id = "'.$network_id.'"
$last_pay_struc_qry ='SELECT prime_pay_structure_id, from_date, max_from_date FROM (SELECT prime_pay_structure_id, from_date, (SELECT MAX(from_date) FROM cw_pay_structure WHERE from_date <= "'.$from_date.'" AND wbs_element = "'.$wbs_element.'" AND personal_code ="'.$personal_code.'" AND POSITION = "'.$position.'" AND trans_status = 1) AS max_from_date FROM cw_pay_structure WHERE from_date <= "'.$from_date.'" AND wbs_element = "'.$wbs_element.'" AND personal_code ="'.$personal_code.'" AND POSITION = "'.$position.'" AND trans_status = 1) AS subquery WHERE from_date = max_from_date;';
$last_pay_struct_info = $this->db->query("CALL sp_a_run ('SELECT','$last_pay_struc_qry')");
$last_pay_struct_rslt = $last_pay_struct_info->result();
$last_pay_struct_info->next_result();
$last_prime_id = (int)$last_pay_struct_rslt[0]->prime_pay_structure_id;
$last_to_date = date("Y-m-d",strtotime("-1 days",strtotime($from_date)));
//and network_id="'.$network_id.'"
$max_date_qry = 'select max(from_date)as max_date FROM cw_pay_structure WHERE from_date >= "'.$from_date.'" and wbs_element="'.$wbs_element.'" and personal_code="'.$personal_code.'" and position="'.$position.'" and trans_status=1';
$max_date_info = $this->db->query("CALL sp_a_run ('SELECT','$max_date_qry')");
$max_date_rslt = $max_date_info->result();
$max_date_info->next_result();
$max_date = $max_date_rslt[0]->max_date;
//PAY STRUCTURE INSERT FUNCTION
if($prime_id === "" || $prime_id === 0){
//GET TO DATE BASED ON EXIST PAYSTRUCTURE FROM DATE and network_id="'.$network_id.'"
$apply_date_check_qry = 'SELECT min(from_date) as from_date,max(from_date)as max_date FROM cw_pay_structure WHERE from_date >= "'.$from_date.'" and wbs_element="'.$wbs_element.'" and personal_code="'.$personal_code.'" and position="'.$position.'" and trans_status=1';
$apply_date_info = $this->db->query("CALL sp_a_run ('SELECT','$apply_date_check_qry')");
$apply_date_rslt = $apply_date_info->result();
$apply_date_info->next_result();
$to_date_rslt = $apply_date_rslt[0]->from_date;
if($to_date_rslt){
$to_date = date('Y-m-t', strtotime($to_date_rslt . ' -1 month'));
}
//for insert a new pay structure
$table_name = 'cw_pay_structure';
$table_col = $ins_qry_key.'category,tax_location,from_date,to_date,effective_date,day_condition,day_count,pf_limit,esi_limit,employee_type,pf_exempt,esi_exempt,lwf_exempt,status,trans_created_by,trans_created_date';
$table_val = '("'.$ins_qry_val.$category.'","'.$tax_location.'","'.$from_date.'","'.$to_date.'","'.$effective_date.'","'.$day_condition.'","'.$day_count.'","'.$pf_limit.'","'.$esi_limit.'","'.$employee_type.'","'.$pf_exempt.'","'.$esi_exempt.'","'.$lwf_exempt.'","1","'.$logged_id.'","'.$date.'")';
$table_where = '';
$ins_pay_struct_rslt = $this->query_ins_function($table_name,$table_col,$table_val,$table_where);
$prime_id = (int)$ins_pay_struct_rslt[0]->ins_id;
$return_success_msg = "Pay Structure Details Successfully Added.!";
}else{
//CONDITION FOR CHECK ONLY OUR LAST PAY STRUCTURE SETTINGS(LAST PRIME ID) SHOULD ONLY UPDATE FOR THAT COMPONENTS
if($prime_id){
// $upd_pay_struct_rslt = $this->query_upd_function($table_name,$table_key_val,$table_where);
$upd_pay_struct_qry = 'UPDATE cw_pay_structure SET activity_no = "'.$activity_no.'",category = "'.$category.'",tax_location = "'.$tax_location.'",from_date = "'.$from_date.'",to_date = "'.$to_date.'",effective_date = "'.$effective_date.'",day_condition = "'.$day_condition.'",day_count = "'.$day_count.'",pf_limit = "'.$pf_limit.'",esi_limit = "'.$esi_limit.'",employee_type = "'.$employee_type.'",pf_exempt = "'.$pf_exempt.'",esi_exempt = "'.$esi_exempt.'",lwf_exempt = "'.$lwf_exempt.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" WHERE cw_pay_structure.prime_pay_structure_id = "'.$prime_id.'" and cw_pay_structure.trans_status = 1';
$upd_pay_struct_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_pay_struct_qry')");
$return_success_msg = "Pay Structure Details Successfully Updated.!";
//------------------------------------end----------------------------------//
}else{
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Don't Modify? Already New Pay Structure Exist for this Components..!"));
exit(0);
}
}
if($last_prime_id){// && $max_date < $chk_frm_date
if($last_prime_id < $prime_id){
//update qry for to date should update to last pay structure row data
$upd_last_pay_struct_qry = 'UPDATE cw_pay_structure SET cw_pay_structure.to_date = "'.$last_to_date.'",cw_pay_structure.status = "2",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" WHERE cw_pay_structure.prime_pay_structure_id = "'.$last_prime_id.'" and cw_pay_structure.trans_status = 1';
$upd_last_pay_struct_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_last_pay_struct_qry')");
}
}
$max_to_date_qry ='UPDATE cw_pay_structure SET status = CASE WHEN to_date = (SELECT MAX(to_date) FROM (SELECT * FROM cw_pay_structure WHERE wbs_element = "'.$wbs_element.'" AND personal_code ="'.$personal_code.'" AND POSITION = "'.$position.'" AND network_id = "'.$network_id.'" and activity_no = "'.$activity_no.'" AND category = "'.$category.'" and trans_status = 1) as ps) THEN 1 ELSE 2 END WHERE wbs_element = "'.$wbs_element.'" AND personal_code ="'.$personal_code.'" AND POSITION = "'.$position.'" AND network_id = "'.$network_id.'" AND category = "'.$category.'" and activity_no = "'.$activity_no.'" and trans_status = 1';
$upd_pay_struct_sts = $this->db->query("CALL sp_a_run ('UPDATE','$max_to_date_qry')");
}
}
//if insert or update as success then show a pay structure wage details
if($prime_id){
//FUNCTION FOR INSERT A PAY STRUCTURE LINE DATA
$ins_upd_pay_struct_line = $this->save_pay_structure_line($prime_id,$json_ear_arr,$json_ded_arr,$category,$pf_exempt,$esi_exempt,$lwf_exempt,$gross_amt_arr,$wage_gross_amt);
// ,$pf_exempt,$esi_exempt,$lwf_exempt
}else{
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Insert Error..!"));
}
}
if($ins_upd_pay_struct_line || $action === "edit" || $action === "view" || $action === "copy"){
if($action === "edit" || $action === "view" || $action === "copy"){
$return_success_msg = "Pay Details are Display Below..!";
}
//PAY STRUCTURE SELECT QRY FOR GET A PF AND ESI AND LWF EXEMPTION CHECKBOX VALUE
$pay_line_rslt = $this->pay_struct_select_fun("cw_pay_structure",$prime_id);
$pf_exempt = (int)$pay_line_rslt[0]['pf_exempt'];
$esi_exempt = (int)$pay_line_rslt[0]['esi_exempt'];
$lwf_exempt = (int)$pay_line_rslt[0]['lwf_exempt'];
$lwf_exempt = (int)$pay_line_rslt[0]['lwf_exempt'];
$lwf_exempt = (int)$pay_line_rslt[0]['lwf_exempt'];
$lwf_exempt = (int)$pay_line_rslt[0]['lwf_exempt'];
$personal_code = $pay_line_rslt[0]['personal_code'];
$wbs_element = $pay_line_rslt[0]['wbs_element'];
$position = $pay_line_rslt[0]['position'];
$activity_no = $pay_line_rslt[0]['activity_no'];
$network_id = $pay_line_rslt[0]['network_id'];
$tax_location = $pay_line_rslt[0]['tax_location'];
//FUNCTION FOR CREATE A EARNINGS AND DEDUCTION TABLE FORMAT
$ear_ded_tab_info = $this->pay_struct_line_table_create($prime_id,$category,$pf_exempt,$esi_exempt,$lwf_exempt);
// ,cw_pay_structure_line.field_type
if(!empty($ear_ded_tab_info)){
echo json_encode(array('success' => true, 'ear_table_rslt' => $ear_ded_tab_info['earnings'], 'ded_table_rslt' => $ear_ded_tab_info['deduction'],'drop_select_arr' => $ear_ded_tab_info['drop_select'],"prime_id" => $prime_id,'pf_exempt' => $pf_exempt,'esi_exempt' => $esi_exempt,'lwf_exempt' => $lwf_exempt,'personal_code' => $personal_code,'wbs_element' => $wbs_element,'position' => $position,'activity_no' => $activity_no,'network_id' => $network_id,'tax_location' => $tax_location, 'message' => "$return_success_msg",'action' => $action));
// Pay Details are Display Below..!
}else{
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Payroll Formula or Paystructure Input Choose Process not Added for this Category..!"));
}
}else{
echo json_encode(array('success' => false, 'message' => "Pay Structure Line Data Update Error..!"));
}
}
public function save_pay_structure_line($prime_pay_struct_id,$json_ear_arr,$json_ded_arr,$category,$pf_exempt,$esi_exempt,$lwf_exempt,$gross_amt_arr,$wage_gross_amt){
$json_ear_arr = json_decode($this->input->post('json_ear_obj'),true);
$json_ded_arr = json_decode($this->input->post('json_ded_obj'),true);
$logged_id = $this->logged_id;
$date = date("Y-m-d H:i:s");
$pay_line_arr = array();
$ear_arr = array();
$ded_arr = array();
$ear_exist_arr = array();
$ded_exist_arr = array();
//for calculate a total earnings gross amount for update to prime paystructure table
//----------- DR CODE START FOR GROSS AMOUNT CALCULATE ----------------------------------
//this array using to calculate a include gross amount only based on wage map hrms module
// $gross_amt_arr = array_column($json_ear_arr, 'amount', 'hrms_field_name');
//for calculate a total earnings gross amount for update to prime paystructure table
$tot_ear_gross = array_sum($gross_amt_arr);
// $tot_ear_gross = number_format((float)$tot_ear_gross, 2, '.', '');
// DR CODE FOR PF AND ESI GROSS CALCULATION 18OCT22 START
$tot_pf_gross_amt = 0;
$tot_esi_gross_amt = 0;
if($pf_exempt === 2){
$pf_gross_arr = array();
foreach ($json_ear_arr as $key => $value) {
if((int)$value['pf_applicable'] === 1){
$pf_gross_arr[$value['hrms_field_name']] = $value['amount'];
}
}
$tot_pf_gross_amt = array_sum($pf_gross_arr);
}
//Select ESI Ceiling Applicable Components
$wage_map_qry = 'select hrms_field_name,wage_name from cw_wage_map_hrms where trans_status = 1 and cw_wage_map_hrms.esi_ceiling_applicable = 1';
$wage_map_data = $this->db->query("CALL sp_a_run ('SELECT','$wage_map_qry')");
$wage_map_result = $wage_map_data->result_array();
$wage_map_data->next_result();
$wage_map_arr = array();
foreach($wage_map_result as $key => $value){
$wage_map_arr[$value['hrms_field_name']] = $value['wage_name'];
}
if($esi_exempt === 2){
$esi_gross_arr = array();
foreach ($json_ear_arr as $key => $value) {
if($wage_map_arr[$value['hrms_field_name']]){
$esi_gross_arr[$value['hrms_field_name']] = $value['amount'];
}
}
$tot_esi_gross_amt = array_sum($esi_gross_arr);
}
//----------- DR CODE END FOR GROSS AMOUNT CALCULATE END ----------------------------------
if($prime_pay_struct_id){
//SELECT QUERY TO GET A PAY STRUCTURE LINE TABLE DATA FOR EXIST CHECK
$pay_line_rslt = $this->pay_struct_select_fun("cw_pay_structure_line",$prime_pay_struct_id);
if($pay_line_rslt[0]){
$pay_line_arr = array();
foreach ($pay_line_rslt as $key => $value) {
$pay_line_arr[$value['wage_code']] = $value['wage_name'];
}
$ear_arr = array();
foreach ($json_ear_arr as $key => $value){
$ear_arr[$value['wage_code']] = $value;
}
$ded_arr = array();
foreach ($json_ded_arr as $key => $value){
$ded_arr[$value['wage_code']] = $value;
}
$ear_exist_arr = array_diff_key($ear_arr,$pay_line_arr);
$ded_exist_arr = array_diff_key($ded_arr,$pay_line_arr);
//IF EDIT FUNCTION BUT NEW WAGES ADD SO WE ALSO CREATE A INSERT FUNCTION AT THE TIME OF EDIT
if($ear_exist_arr || $ded_exist_arr){
$action = "insert";
$pay_line_ins_qry_val = $this->ins_upd_qry_build_from_array($prime_pay_struct_id,$action,$ear_exist_arr,$ded_exist_arr,$logged_id,$date);
if($pay_line_ins_qry_val){
$table_name = 'cw_pay_structure_line';
$table_col = 'prime_pay_structure_id,field_type,wage_code,wage_name,hrms_field_name,amount,pf_applicable,esi_applicable,pt_applicable,trans_created_by,trans_created_date';
$table_val = ''.$pay_line_ins_qry_val.'';
$table_where = '';
$ins_pay_line_rslt = $this->query_ins_function($table_name,$table_col,$table_val,$table_where);
$prime_line_id = (int)$ins_pay_line_rslt[0]->ins_id;
//if new wage came after inserted and THEN (don't update becuase already INSERTED) @6sep22@
if($prime_line_id){
foreach($json_ear_arr as $key => $val){
$wage_code = $val['wage_code'];
if($ear_exist_arr[$wage_code]){
unset($json_ear_arr[$key]);
}
}
foreach($json_ded_arr as $key => $val){
$wage_code = $val['wage_code'];
if($ded_exist_arr[$wage_code]){
unset($json_ded_arr[$key]);
}
}
}
//end
}
}
//UPDATE FUCNTION
$action = "update";
$pay_line_qry_val = $this->ins_upd_qry_build_from_array($prime_pay_struct_id,$action,$json_ear_arr,$json_ded_arr,$logged_id,$date);
if($pay_line_qry_val){
$pay_struct_line_upd_qry = 'INSERT INTO cw_pay_structure_line (prime_pay_structure_line_id,prime_pay_structure_id,field_type,wage_code,wage_name,hrms_field_name,amount,pf_applicable,esi_applicable,pt_applicable,trans_updated_by,trans_updated_date) VALUES '.$pay_line_qry_val.' ON DUPLICATE KEY UPDATE prime_pay_structure_id = VALUES(prime_pay_structure_id),field_type = VALUES(field_type),wage_code = VALUES(wage_code),wage_name = VALUES(wage_name),hrms_field_name = VALUES(hrms_field_name),amount = VALUES(amount),pf_applicable = VALUES(pf_applicable),esi_applicable = VALUES(esi_applicable),pt_applicable = VALUES(pt_applicable),trans_updated_by = VALUES(trans_updated_by),trans_updated_date = VALUES(trans_updated_date)';
$pay_struct_line_upd_info = $this->db->query("CALL sp_a_run ('RUN','$pay_struct_line_upd_qry')");
if($pay_struct_line_upd_info){
//UPDATE QRY FOR UPDATE A TOTAL GROSS AMOUNT TO PAYSTRUCTURE TABLE
$upd_gross_amt_qry = 'UPDATE cw_pay_structure SET ear_gross = "'.$tot_ear_gross.'",gross = "'.$wage_gross_amt.'",pf_gross = "'.$tot_pf_gross_amt.'",esi_gross = "'.$tot_esi_gross_amt.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" WHERE cw_pay_structure.prime_pay_structure_id = "'.$prime_pay_struct_id.'" and cw_pay_structure.trans_status = 1';
$upd_gross_amt_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_gross_amt_qry')");
return true;
}else{
return true;
}
}else{
return false;
}
}else{
//PAY STRUCTURE LINE INSERT
$action = "insert";
$pay_line_ins_qry_val = $this->ins_upd_qry_build_from_array($prime_pay_struct_id,$action,$json_ear_arr,$json_ded_arr,$logged_id,$date);
if($pay_line_ins_qry_val){
$table_name = 'cw_pay_structure_line';
$table_col = 'prime_pay_structure_id,field_type,wage_code,wage_name,hrms_field_name,amount,pf_applicable,esi_applicable,pt_applicable,trans_created_by,trans_created_date';
$table_val = ''.$pay_line_ins_qry_val.'';
$table_where = '';
$ins_pay_line_rslt = $this->query_ins_function($table_name,$table_col,$table_val,$table_where);
$prime_line_id = (int)$ins_pay_line_rslt[0]->ins_id;
if($prime_line_id){
//UPDATE QRY FOR UPDATE A TOTAL GROSS AMOUNT TO PAYSTRUCTURE TABLE
$upd_gross_amt_qry = 'UPDATE cw_pay_structure SET ear_gross = "'.$tot_ear_gross.'",gross = "'.$wage_gross_amt.'",pf_gross = "'.$tot_pf_gross_amt.'",esi_gross = "'.$tot_esi_gross_amt.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" WHERE cw_pay_structure.prime_pay_structure_id = "'.$prime_pay_struct_id.'" and cw_pay_structure.trans_status = 1';
$upd_gross_amt_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_gross_amt_qry')");
return true;
}else{
return false;
}
}else{
return false;
}
}
}
}
//AFTER INSERT OR UPDATE THEN SHOW A ALL DATA THROUGH IN TABLE
public function pay_struct_line_table_create($prime_id,$category,$pf_exempt,$esi_exempt,$lwf_exempt){
$return_array = array();
$wage_detail_rslt = $this->check_cat_wage_formula_fun($prime_id,$category);
if($wage_detail_rslt[0]){
//EARNING TABLE STRUCUTRE CREATE
$ear_table_struct = '<table id="ear_pay_struct_table" class="table table-hover">
<thead class="thead-dark">
<tr>
<th></th><th>EARNINGS</th><th></th><th><input type="checkbox" id="pf_exempt" name="pf_exempt" value = "'.$pf_exempt.'"><label class="checkbox-inline" for="pf_exempt"><strong>PF Exempt</strong></label></th><th><input type="checkbox" id="esi_exempt" name="esi_exempt" value = "'.$esi_exempt.'"><label class="checkbox-inline" for="esi_exempt"><strong>ESI Exempt</strong></label></th><th><input type="checkbox" id="lwf_exempt" name="lwf_exempt" value = "'.$lwf_exempt.'"><label class="checkbox-inline" for="lwf_exempt"><strong>LWF Exempt</strong></label></th>
</tr>
<tr>
<th scope="col">Wage Code</th>
<th scope="col">Wage Name</th>
<th scope="col">Amount</th>
<th scope="col">PF Applicable</th>
<th scope="col">ESI Applicable</th>
<th scope="col">PT Applicable</th>
<th scope="col" style = "display:none;">Field Name</th>
<th scope="col" style = "display:none;">Prime Pay Line Id</th>
<th scope="col" style = "display:none;">Prime Pay Id</th>
</tr>
</thead>
<tbody>';
$ear_table_data = implode('</tr>', array_map(function ($v) use($prime_id) {
//CONDITIONS ONLY FOR CREATE AND ALTER QUERY FORMAT
if((int)$v['formula_type'] === 1){
$ear_table_body = '<tr><td><input type="text" class="form-control" id="wage_code" name="wage_code" value ="'.$v['wage_code'].'" class="form-control" readonly = "readonly" size="2"></td><td><input type="text" class="form-control" id="wage_name" name="wage_name" value ="'.$v['wage_name'].'" class="form-control" readonly = "readonly" style = "width:105%;"></td><td><input type="decimals" class="form-control decimals" id="amount" name="amount" value ="'.$v['amount'].'" size="6" autocomplete="off"></td><td><select id="pf_applicable" name="pf_applicable" data-val = "'.$v['pf_applicable'].'" class="form-control select"></select></td><td><select id="esi_applicable" name="esi_applicable" data-val = "'.$v['esi_applicable'].'" class="form-control select"></select></td><td><select id="pt_applicable" name="pt_applicable" data-val = "'.$v['pt_applicable'].'" class="form-control select"></select></td><td style = "display:none;"><input type="hidden" class="form-control" id="hrms_field_name" name="hrms_field_name" value = "'.$v['hrms_field_name'].'" class="form-control"></td><td style = "display:none;"><input type="hidden" class="form-control" id="prime_pay_line_id" name="prime_pay_line_id" value = "'.$v['prime_pay_line_id'].'" class="form-control"></td><td style = "display:none;"><input type="hidden" class="form-control" id="prime_pay_struct_id" name="prime_pay_struct_id" value = "'.$prime_id.'"></td>';
return $ear_table_body;
}
}, $wage_detail_rslt));
$ded_table_struct = '<table id="ded_pay_struct_table" class="table table-hover">
<thead class="thead-dark">
<tr>
<th></th><th scope="col" style = " text-align: center;">DEDUCTION</th><th></th>
</tr>
<tr>
<th scope="col">Wage Code</th>
<th scope="col">Wage Name</th>
<th scope="col">Amount</th>
<th scope="col" style = "display:none;">Prime Pay Line Id</th>
<th scope="col" style = "display:none;">Prime Pay Id</th>
</tr>
</thead>
<tbody>';
$ded_table_data = implode('</tr>', array_map(function ($v) use($prime_id) {
//CONDITIONS ONLY FOR CREATE AND ALTER QUERY FORMAT
if((int)$v['formula_type'] === 2){
$ded_table_body = '<tr><td><input type="text" class="form-control" id="wage_code" name="wage_code" value ="'.$v['wage_code'].'" class="form-control" readonly = "readonly"></td><td><input type="text" class="form-control" id="wage_name" name="wage_name" value ="'.$v['wage_name'].'" class="form-control" readonly = "readonly"></td><td><input type="decimals" class="form-control" id="amount" name="amount" value ="'.$v['amount'].'" size="5" autocomplete="off"></td><td style = "display:none;"><input type="hidden" class="form-control" id="hrms_field_name" name="hrms_field_name" value = "'.$v['hrms_field_name'].'" class="form-control"></td><td style = "display:none;"><input type="hidden" class="form-control" id="prime_pay_line_id" name="prime_pay_line_id" value = "'.$v['prime_pay_line_id'].'" class="form-control"></td><td style = "display:none;"><input type="hidden" class="form-control" id="prime_pay_struct_id" name="prime_pay_struct_id" value = "'.$prime_id.'"></td>';
return $ded_table_body;
}
}, $wage_detail_rslt));
//ARRAY FOR SELECT A DROPDOWN VALUE FOR PF,ESI AND PT
$drop_selct_arr = array();
foreach ($wage_detail_rslt as $key => $value){
$drop_selct_arr[$value['prime_pay_line_id']][$value['wage_code']] = $value;
}
if($ear_table_data && $ded_table_data){
$ear_table_details = $ear_table_struct.$ear_table_data;
$ded_table_details = $ded_table_struct.$ded_table_data;
$return_array["earnings"] = $ear_table_details;
$return_array["deduction"] = $ded_table_details;
$return_array["drop_select"] = $drop_selct_arr;
return $return_array;
}else
if($ear_table_data){
$ear_table_details = $ear_table_struct.$ear_table_data;
$return_array["earnings"] = $ear_table_details;
$return_array["drop_select"] = $drop_selct_arr;
return $return_array;
}else
if($ded_table_data){
$ded_table_details = $ded_table_struct.$ded_table_data;
$return_array["deduction"] = $ded_table_details;
$return_array["drop_select"] = $drop_selct_arr;
return $return_array;
}else{
return $return_array();
}
}
}
//PAY STRUCTURE COMPONENTS ENABLE CHECK QRY FUNCTION
public function check_cat_wage_formula_fun($prime_id,$category){
$wage_detail_rslt = "";
// inner join cw_payroll_formula on cw_payroll_formula.
if($prime_id !== 0 && $prime_id !== ""){
$select_columns = 'cw_wage_map_hrms.wage_code,cw_wage_map_hrms.wage_name,cw_wage_map_hrms.hrms_field_name,(CASE WHEN cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" THEN cw_pay_structure_line.amount ELSE "0" END) as amount,(CASE WHEN cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" THEN cw_pay_structure_line.pf_applicable ELSE cw_wage_map_hrms.pf_applicable END) as pf_applicable,(CASE WHEN cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" THEN cw_pay_structure_line.esi_applicable ELSE cw_wage_map_hrms.esi_applicable END) as esi_applicable,(CASE WHEN cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" THEN cw_pay_structure_line.pt_applicable ELSE cw_wage_map_hrms.pt_applicable END) as pt_applicable,(CASE WHEN cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" THEN cw_pay_structure_line.prime_pay_structure_line_id ELSE "" END) as prime_pay_line_id,cw_payroll_formula.formula_type';
$table_name = 'cw_pay_structure_line';
$table_join = 'right join cw_wage_map_hrms on (cw_pay_structure_line.wage_code = cw_wage_map_hrms.wage_code and cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" and cw_pay_structure_line.trans_status = 1) inner join cw_form_setting on (cw_form_setting.label_name = cw_wage_map_hrms.hrms_field_name and FIND_IN_SET('.$category.', cw_form_setting.field_for)) inner join cw_payroll_formula on cw_payroll_formula.out_column = cw_wage_map_hrms.hrms_field_name';
$table_where = 'cw_form_setting.prime_module_id = "employees" and cw_form_setting.paystructure_check = 1 and cw_payroll_formula.formula_for = "'.$category.'" and cw_payroll_formula.formula_mode = 1 and cw_form_setting.trans_status = 1 and cw_payroll_formula.trans_status = 1 and cw_wage_map_hrms.trans_status = 1 ORDER BY cw_payroll_formula.order_by ASC';
$wage_detail_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
}else{
$select_columns = 'cw_wage_map_hrms.wage_code,cw_wage_map_hrms.wage_name,cw_wage_map_hrms.hrms_field_name,0 as amount,cw_wage_map_hrms.pf_applicable as pf_applicable,cw_wage_map_hrms.esi_applicable as esi_applicable,cw_wage_map_hrms.pt_applicable as pt_applicable,cw_payroll_formula.formula_type,0 as prime_pay_line_id';
$table_name = 'cw_wage_map_hrms';
$table_join = ' inner join cw_form_setting on (cw_form_setting.label_name = cw_wage_map_hrms.hrms_field_name and FIND_IN_SET('.$category.', cw_form_setting.field_for)) inner join cw_payroll_formula on cw_payroll_formula.out_column = cw_wage_map_hrms.hrms_field_name';
$table_where = 'cw_form_setting.prime_module_id = "employees" and cw_form_setting.paystructure_check = 1 and cw_payroll_formula.formula_for = "'.$category.'" and cw_payroll_formula.formula_mode = 1 and cw_form_setting.trans_status = 1 and cw_payroll_formula.trans_status = 1 and cw_wage_map_hrms.trans_status = 1 ORDER BY cw_payroll_formula.order_by ASC';
$wage_detail_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
}
return $wage_detail_rslt;
}
public function ins_upd_qry_build_from_array($prime_pay_id,$action,$json_ear_arr,$json_ded_arr,$logged_id,$date){
$ear_upd_qry = "";
$ded_upd_qry = "";
//earning ins query build function
if(!empty($json_ear_arr)){
$ear_upd_qry = implode(',"'.$logged_id.'","'.$date.'"),(', array_map(function ($ear) use($prime_pay_id){
if((int)$ear['prime_pay_line_id']){
$ear_qry = '"'.$ear['prime_pay_line_id'].'","'.$prime_pay_id.'","earnings","'.$ear['wage_code'].'","'.$ear['wage_name'].'","'.$ear['hrms_field_name'].'","'.$ear['amount'].'","'.$ear['pf_applicable'].'","'.$ear['esi_applicable'].'","'.$ear['pt_applicable'].'"';
}else{
$ear_qry = '"'.$prime_pay_id.'","earnings","'.$ear['wage_code'].'","'.$ear['wage_name'].'","'.$ear['hrms_field_name'].'","'.$ear['amount'].'","'.$ear['pf_applicable'].'","'.$ear['esi_applicable'].'","'.$ear['pt_applicable'].'"';
}
return $ear_qry;
}, $json_ear_arr));
if($ear_upd_qry){
$ear_upd_qry = '('.$ear_upd_qry.',"'.$logged_id.'","'.$date.'")';
}
}
//deduction ins query build function
if(!empty($json_ded_arr)){
$ded_upd_qry = implode(',"'.$logged_id.'","'.$date.'"),(', array_map(function ($ded) use($prime_pay_id) {
if((int)$ded['prime_pay_line_id']){
$ded_qry = '"'.$ded['prime_pay_line_id'].'","'.$prime_pay_id.'","deduction","'.$ded['wage_code'].'","'.$ded['wage_name'].'","'.$ded['hrms_field_name'].'","'.$ded['amount'].'","'.$ded['pf_applicable'].'","'.$ded['esi_applicable'].'","'.$ded['pt_applicable'].'"';
}else{
$ded_qry = '"'.$prime_pay_id.'","deduction","'.$ded['wage_code'].'","'.$ded['wage_name'].'","'.$ded['hrms_field_name'].'","'.$ded['amount'].'","'.$ded['pf_applicable'].'","'.$ded['esi_applicable'].'","'.$ded['pt_applicable'].'"';
}
return $ded_qry;
}, $json_ded_arr));
if($ded_upd_qry){
$ded_upd_qry = '('.$ded_upd_qry.',"'.$logged_id.'","'.$date.'")';
}
}
if($ear_upd_qry && $ded_upd_qry){
return $ear_upd_qry.','.$ded_upd_qry;
}else
if($ear_upd_qry){
return $ear_upd_qry;
}else
if($ded_upd_qry){
return $ded_upd_qry;
}else{
return "";
}
}
public function fetch_drop_list(){
$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);
}
$input_name = $this->input->post('input_name');
$input_data = $this->input->post('input_data');
$pay_column_rslt = $this->pay_add_filter_col_setting();
$network_number = "";
if($input_name === "activity_no"){
$wbs_id_qry = 'select cw_sap_wbs.wbs_id,cw_sap_wbs.wbs_network_id from cw_sap_wbs where trans_status = 1 and cw_sap_wbs.wbs_id = "'.$input_data.'"';
$wbs_id_data = $this->db->query("CALL sp_a_run ('SELECT','$wbs_id_qry')");
$wbs_id_result = $wbs_id_data->result();
$wbs_id_data->next_result();
$wbs_id = $wbs_id_result[0]->wbs_id;
$input_data = $wbs_id;
$network_number = $wbs_id_result[0]->wbs_network_id;
//data for network column fetch based on wbs element
}
$depen_drop_list = $this->dependent_pick_col_function($input_data,$input_name,$pay_column_rslt);
if($depen_drop_list){
// , 'network_number' => $network_number
echo json_encode(array('success' => true, 'depen_drop_list' => $depen_drop_list, 'network_number' => $network_number));
}
}
//IMPORT FILE VIEW INFORMATION
public function import(){
$data = array();
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/import",$data);
}
public function pay_excel(){
$obj = new Spreadsheet();
$excel_name = "pay_structure_import_details";
$db_name = $this->config->item("db_name");
$row_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name,COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = 'cw_pay_structure' AND COLUMN_NAME NOT IN ('trans_created_by','trans_created_date','trans_updated_by','trans_updated_date','trans_deleted_by','trans_deleted_date','trans_status','prime_pay_structure_id','ear_gross','gross','pf_gross','esi_gross','effective_date','day_count') order by ORDINAL_POSITION ASC");
$row_table_col = $row_table_query->result();
$col_map_name = 'A';
$obj->getActiveSheet()->getCell($col_map_name."1")->setValue("SL NO");
$col_map_name = 'B';
foreach($row_table_col as $excel){
$excel_line_column_name = ucwords(str_replace("_"," ",$excel->col_name));
if($excel_line_column_name === "Status"){
$excel_line_column_name = "Active Status";
}
$obj->getActiveSheet()->getCell($col_map_name."1")->setValue($excel_line_column_name);
$col_map_name++;
}
//QRY FOR PAY STRUCTURE LINE COLUMN ADD TO EXCEL
// DISTINCT(cw_wage_map_hrms.wage_name)
$pay_struct_line_qry = 'SELECT DISTINCT cw_wage_map_hrms.wage_name,label_name,view_name FROM `cw_payroll_formula` INNER JOIN cw_form_setting on cw_form_setting.label_name = cw_payroll_formula.out_column inner join cw_wage_map_hrms on cw_wage_map_hrms.hrms_field_name = cw_form_setting.label_name WHERE cw_payroll_formula.order_by != 0 and cw_payroll_formula.formula_mode = 1 and cw_form_setting.paystructure_check = 1 and cw_payroll_formula.trans_status = 1 and cw_form_setting.trans_status = 1 ORDER BY order_by ASC';
$pay_struct_line_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_struct_line_qry')");
$pay_struct_line_rslt = $pay_struct_line_info->result();
$pay_struct_line_info->next_result();
for($i = 0; $i <= 3; $i++){
if($i === 0){
$content = " Amount";
}else
if($i === 1){
$content = " PF Applicable";
}else
if($i === 2){
$content = " ESI Applicable";
}else
if($i === 3){
$content = " PT Applicable";
}
foreach($pay_struct_line_rslt as $pay_struct_line){
$excel_line_column_name = ucwords(str_replace("_"," ",$pay_struct_line->wage_name));
$obj->getActiveSheet()->getCell($col_map_name."1")->setValue($excel_line_column_name.$content);
$col_map_name++;
}
}
ob_end_clean();
// Rename worksheet name
$filename= $excel_name.".xls"; //save our workbook as this file name
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename= "'.$filename.'"');
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($obj, 'Xls');
$writer->save('php://output');
echo json_encode(array('success' => TRUE, 'output' => $obj));
exit(0);
}
//FUNCTION FOR USING DYNAMICALLY BUILD MULTIDIMENSIONAL ARRAY
public function add_array_keys_dynamic($main_array, $keys, $value){
$tmp_array = &$main_array;
while( count($keys) > 0 ){
$k = array_shift($keys);
// if(!is_array($tmp_array)){
// $tmp_array = array();
// }
$tmp_array = &$tmp_array[$k];
}
$tmp_array = $value;
return $main_array;
}
//FUNCTION FOR VALUE EXIST CHECKING IN MULTIDIMENSIONAL ARRAY KEY BASED
public function multi_dimens_arr_key_exist($multi_dimens_arr,$check_key_data_arr){
$current = $multi_dimens_arr;
foreach($check_key_data_arr as $check_key){
$current = $current[$check_key];
}
if($current){
return $current;
}else{
return '';
}
}
//EXCEL IMPORT FUNCTION
public function excel_file_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);
}
$file_path = $this->input->post("file_path");
$db_name = $this->config->item("db_name");
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
try{
$excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($file_path);
}catch(Exception $e){
die('Error loading file "' . pathinfo($file_path, PATHINFO_BASENAME). '": ' . $e->getMessage());
return json_encode(array('success' => false, 'message' => "Invalid file or path"));
}
$prime_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = 'cw_pay_structure' AND COLUMN_NAME NOT IN ('trans_created_by','trans_created_date','trans_updated_by','trans_updated_date','trans_deleted_by','trans_deleted_date','trans_status','prime_pay_structure_id','ear_gross','gross','pf_gross','esi_gross','effective_date','day_count')");
// and COLUMN_NAME not like '%trans%'
$prime_table_col = $prime_table_query->result_array();
$pay_name_arr = array();
foreach($prime_table_col as $col_val){
// $col_name = $col_val['col_name'];
$show_name = ucwords(str_replace("_"," ",$col_val['col_name']));
$pay_name_arr[$show_name] = $show_name;
}
//pay structure line columns get (based on form setting and payroll formula and wage map hrms table)
$pay_struct_line_qry = 'SELECT label_name,view_name,cw_wage_map_hrms.wage_name,cw_wage_map_hrms.wage_code,cw_form_setting.input_for FROM `cw_payroll_formula` INNER JOIN cw_form_setting on cw_form_setting.label_name = cw_payroll_formula.out_column inner join cw_wage_map_hrms on cw_wage_map_hrms.hrms_field_name = cw_form_setting.label_name WHERE cw_payroll_formula.order_by != 0 and cw_payroll_formula.formula_mode = 1 and cw_form_setting.paystructure_check = 1 and cw_payroll_formula.trans_status = 1 and cw_form_setting.trans_status = 1 ORDER BY order_by ASC';
$pay_struct_line_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_struct_line_qry')");
$pay_struct_line_rslt = $pay_struct_line_info->result_array();
$pay_struct_line_info->next_result();
$pay_line_name_arr = array();
$pay_struct_line_arr = array();
foreach($pay_struct_line_rslt as $line_val){
// $col_name = $col_val['col_name'];
$wage_name = ucwords(str_replace("_"," ",$line_val['wage_name']));
// $pay_line_name_arr[$wage_name] = $wage_name;
$pay_struct_line_arr[$wage_name] = $line_val;
}
//FOR GET PAY STRUCTURE LINE COLUMN
for($i = 0; $i <= 3; $i++){
if($i === 0){
$content = " Amount";
}else
if($i === 1){
$content = " PF Applicable";
}else
if($i === 2){
$content = " ESI Applicable";
}else
if($i === 3){
$content = " PT Applicable";
}
foreach($pay_struct_line_rslt as $line_val){
$wage_name = ucwords(str_replace("_"," ",$line_val['wage_name']));
$pay_line_name_arr[$wage_name.$content] = $wage_name.$content;
}
}
//TWO ARRAY CONVERT TO ONE ARRAY(GET PAY STRUCTURE AND LINE HEADER NAME)
$all_pay_name_arr = $pay_name_arr + $pay_line_name_arr;
//QRY FOR GROSS AMT CALCULATE BASED ON WAGE MAP HRMS TABLE
$gross_wage_map_qry = 'select cw_wage_map_hrms.wage_name,cw_wage_map_hrms.hrms_field_name from cw_wage_map_hrms where cw_wage_map_hrms.include_gross = "1" and cw_wage_map_hrms.trans_status = 1';
$gross_wage_map_info = $this->db->query("CALL sp_a_run ('SELECT','$gross_wage_map_qry')");
$gross_wage_map_rslt = $gross_wage_map_info->result_array();
$gross_wage_map_info->next_result();
$gross_wage_arr = array();
foreach ($gross_wage_map_rslt as $key => $value){
$gross_wage_arr[$value['hrms_field_name']] = $value['hrms_field_name'];
}
//sap activity number
$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';
$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();
$sap_act_no_arr = array();
foreach ($sap_act_no_rslt as $key => $value){
$sap_act_no_arr[$value['act_wbs_id']][$value['act_no']] = $value['prime_id'];
}
//SELECT QUERY FOR GET A PAY STRUCTURE EXIST CHECK COLUMNS GET
$imp_check_col_qry = 'select pay_struct_input from cw_pay_structure_settings where import_check_column = "1" and trans_status = "1" ORDER BY input_order ASC';
// and transaction_type = 1
$imp_check_col_data = $this->db->query("CALL sp_a_run ('SELECT','$imp_check_col_qry')");
$imp_check_col_rslt = $imp_check_col_data->result_array();
$imp_check_col_data->next_result();
$imp_check_col_arr = array_column($imp_check_col_rslt,"pay_struct_input","pay_struct_input");
//Select ESI Ceiling Applicable Components
$wage_map_qry = 'select hrms_field_name,wage_name from cw_wage_map_hrms where trans_status = 1 and cw_wage_map_hrms.esi_ceiling_applicable = 1';
$wage_map_data = $this->db->query("CALL sp_a_run ('SELECT','$wage_map_qry')");
$wage_map_result = $wage_map_data->result_array();
$wage_map_data->next_result();
$wage_map_arr = array();
foreach($wage_map_result as $key => $value){
$wage_map_arr[$value['hrms_field_name']] = $value['wage_name'];
}
//ARRAY FOR CHECK A IMPORT PICKLIST DATA ARE VALID OR NOT
$form_info_qry = 'select * from cw_form_setting where prime_module_id = "employees" and field_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and field_type in (5,7,9) and label_name not in ("activity_no") ORDER BY input_for,field_sort ASC';
// and transaction_type = 1
$form_info_data = $this->db->query("CALL sp_a_run ('SELECT','$form_info_qry')");
$form_info_rslt = $form_info_data->result();
$form_info_data->next_result();
$type = '';
//FUNCTION FOR GET A FROM SETTING PICKLIST 18OCT22 START
$all_pick_import_arr = $this->pay_picklist_get_function("import",$form_info_rslt,$type);
//day condition picklist array
$day_cond_arr = $this->day_cond_qry_struct_function();
//status mode picklist array
$status_mode_arr = $this->status_mode_qry_struct_function();
$sheet = $excel_obj->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$highest_column = $sheet->getHighestColumn();
$worksheetTitle = $sheet->getTitle();
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highest_column);
$key_data = array();
$status_array = array();
$key_val_arr = array();
$sts = array();
$error_info = array();
$excel_line_col = array();
$pay_struct_val = "";
$count = 0;
$head_count = 0;
$old_pay_id_arr = array();
$head_with_out_check_arr = array("SL NO","Active Status");
//FOR EXCEL DUPLICATE
$excel_row_exist_arr = array();
for ($row = 1; $row <= $highestRow; $row++) {
$key_val_data = array();
$col_data = array();
$status_info = array();
$status_info["Excel Row"] = $row;
$check_sts = 1;
$mi_where_qry = '';
$pay_str_where_qry = '';
$from_date_col = '';
$to_date_col = '';
for ($col = 0; $col < $highestColumnIndex; $col++) {
$data = $sheet->getCellByColumnAndRow($col, $row)->getValue();
//FOR GET COLUMN NAME LIKE A,B,C ETC
if($row === 1) {
// Header row. Save it in "$keys".
$excel_key = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col);
$excel_line_col[$data] = $excel_key;
if($data === "From Date"){
$from_date_col = $col;
}
if($data === "To Date"){
$to_date_col = $col;
}
//HEADER NAME ERROR VALIDATION CHECK
if(!in_array($data,$all_pay_name_arr) && !in_array($data,$head_with_out_check_arr)){
$error_info["error"]["$excel_key$row"] = "Invalid Header ($data) Mapped.!";
$head_count++;
}
}
$col_data[] = $data;
}
//EXCEL HEADER ERRORS
if($head_count){
$table_info = $this->get_excel_error_ui($error_info);
echo json_encode(array('success'=>false,'message'=>"Excel Error",'table_info'=>$table_info));
exit(0);
}else{
if($row === 1) {
// Header row. Save it in "$keys".
$key_data = $col_data;
}
// This is not the first row; so it is a data row.
if($row !== 1 && $key_data){
$key_val_data = array_combine($key_data, $col_data);
//FOR EXCEL DUPLICATE
$excel_col_str_arr = array();
foreach($prime_table_col as $col_val){
$col_name = $col_val['col_name'];
$col_name = ltrim(rtrim($col_name," ")," ");
$dis_col_name = ucwords(str_replace("_"," ",$col_name));
if($col_name === "tax_location"){
$col_name = "professional_tax_location";
}
/*if(!$key_val_data[$dis_col_name]){
$alpha_col_name = $excel_line_col[$dis_col_name];
$error_info["error"]["$row"] = "$col_name Invalid Header Mapped.!";
$count++;
$check_sts = 0;
}else{*/
//pay strucuture import settings module based array
/*--------------------THIS $pick_val_arr LINE DON'T DELETE-------------------------------------*/
// $pick_val_arr = $this->pay_import_arr[$col_name];
//FOR WBS BASED ACTIVITY CHECK
if($col_name === "activity_no"){
if($wbs_element){
$check_val = $key_val_data[$dis_col_name];
$qry_val = $sap_act_no_arr[$wbs_element][$check_val];
if(!$qry_val){
$alpha_col_name = $excel_line_col[$dis_col_name];
$error_info["error"]["$alpha_col_name$row"] = "Column of $dis_col_name is Invalid $check_val Mapped.!";
$count++;
$check_sts = 0;
}
//----------------- DON'T DELETE -------------
/*else{
if(array_key_exists($col_name,$imp_check_col_arr)){
//FOR PAY STRUCTURE
$pay_str_where_qry .= 'cw_pay_structure.'.$col_name.' = "'.$qry_val.'" and ';
//FOR MI FMS
$mi_where_qry .= 'cw_monthly_input_fms.'.$col_name.' = "'.$qry_val.'" and ';
}
}*/
}else{
$alpha_col_name = $excel_line_col[$dis_col_name];
$error_info["error"]["$alpha_col_name$row"] = "First You should Map a WBS Element Column After should Map a Activity Number Column.!";
$count++;
$check_sts = 0;
}
}else{
if (array_key_exists($col_name,$all_pick_import_arr)){
//WBS GET
if($col_name === "wbs_element"){
$wbs_element = $key_val_data[$dis_col_name];
// $wbs_col_name = $dis_col_name;
}
$pick_val_arr = $all_pick_import_arr[$col_name];
//picklist value get from array for insert a pay structure
$check_val = $key_val_data[$dis_col_name];
$qry_val = $pick_val_arr[$check_val];
// if(!$check_val){
// $alpha_col_name = $excel_line_col[$dis_col_name];
// $error_info["error"]["$row"] = "Invalid $dis_col_name Header Name Mapped.!";
// $count++;
// $check_sts = 0;
// }else
if(!$qry_val){
$alpha_col_name = $excel_line_col[$dis_col_name];
$error_info["error"]["$alpha_col_name$row"] = "Column of $dis_col_name is Invalid $check_val Mapped.!";
$count++;
$check_sts = 0;
}else{
if($col_name === "category"){
// $col_name = "role";
$category = $qry_val;
}
if($col_name === "personal_code"){
$personal_code = $key_val_data[$dis_col_name];
}
if($col_name === "position"){
$position = $key_val_data[$dis_col_name];
}
//NAME DIFFER ONLY IN PAY STRUCTURE
if($col_name === "professional_tax_location"){
$col_name = "tax_location";
}
if(array_key_exists($col_name,$imp_check_col_arr) && ($col_name !== "category" || $col_name !== "role") && ($col_name !== "tax_location" || $col_name !== "professional_tax_location")){
//FOR PAY STRUCTURE
$pay_str_where_qry .= 'cw_pay_structure.'.$col_name.' = "'.$qry_val.'" and ';
//FOR MI FMS
$mi_where_qry .= 'cw_monthly_input_fms.'.$col_name.' = "'.$qry_val.'" and ';
//FOR EXCEL DUPLICATE
$excel_col_str_arr[] = $qry_val;
}
}
}
}
// }
// if($error_info["error"]["$row"]){
//FROM DATE GET FOR EXIST VALIDATIONS CHECK
if($col_name === "from_date"){//FROM DATE VALIDATIONS
$from_date = $key_val_data[$dis_col_name];
$alpha_col_name = $excel_line_col[$dis_col_name];
$alpha_from_name = $alpha_col_name;
if(!$from_date && $from_date === null){
$error_info["error"]["$alpha_col_name$row"] = "Column of $dis_col_name should not Empty or Invalid.!";
$count++;
$check_sts = 0;
}else{
$unix_date = ($from_date - 25569) * 86400;
$excel_date = 25569 + ($unix_date / 86400);
$unix_date = ($excel_date - 25569) * 86400;
$from_date = gmdate("Y-m-d", $unix_date);
$start_date = date("d", strtotime($from_date));
//VALIDATION FOR ONLY 01 SHOULD BE ALLOWED FROM DATE
if($start_date !== "01"){
$error_info["error"]["$alpha_col_name$row"] = "Column of $dis_col_name Inbetween date should not be allowed.!";
}else{
$excel_col_str_arr[] = $from_date;
}
//FOR EXCEL DUPLICATE
}
}
if($col_name === "to_date"){//TO DATE VALIDATIONS
$to_date = $key_val_data[$dis_col_name];
//TO DATE NOT GIVEN WE TAKE FUTURE DATE
if(!$to_date && $to_date === null){
$to_date = date("Y-m-d",strtotime("31-12-2099"));
}else{
$unix_date = ($to_date - 25569) * 86400;
$excel_date = 25569 + ($unix_date / 86400);
$unix_date = ($excel_date - 25569) * 86400;
$to_date = gmdate("Y-m-d", $unix_date);
}
// from date and to date validation
if($from_date && $to_date){
if($from_date >= $to_date){
$error_info["error"]["$alpha_from_name$row"] = "To Date should be greater than From Date.!";
$count++;
$check_sts = 0;
}
}else{
$error_info["error"]["$alpha_from_name$row"] = "Column of From Date should not Empty or Invalid.!";
$count++;
$check_sts = 0;
}
}
//DAY CONDITION VALIDATION
if($col_name === "day_condition"){
$day_cond_val = $day_cond_arr[$key_val_data[$dis_col_name]];
$alpha_col_name = $excel_line_col[$dis_col_name];
if(!$day_cond_val){
$error_info["error"]["$alpha_col_name$row"] = "Invalid Day Condition Mapped.!";
$count++;
$check_sts = 0;
}
}
// }
}
//IF ALL COLS COME IN WHERE CONDITIONS THEN ONLY CHECK A MI EXIST AND PAY STRUCTURE EXIST VALIDATIONS
if($check_sts){
if($category && $from_date){
//-------------------- MI EXIST CHECK START -------------------------
$exist_count = 0;
// $payroll_exist_rslt = $this->payroll_exist_check_qry($category,$from_date,$mi_where_qry);
$payroll_exist_rslt = $this->payroll_exist_check_qry($category,$from_date,$personal_code,$wbs_element,$position);
// ,$table_where_qry
if($payroll_exist_rslt[0]->count){
$exist_count = (int)$payroll_exist_rslt[0]->count;
}
if($exist_count){
$error_info["error"]["$row"] = "Pre Audit Completed for this month..!";
$count++;
}else{
//------------------- PAY STRUCTURE EXIST CHECK START -------------------------
//cw_pay_structure.category = "'.$category.'" and
$pay_str_exist_qry = 'select cw_pay_structure.prime_pay_structure_id as prime_id from cw_pay_structure where '.$pay_str_where_qry.' cw_pay_structure.from_date >= "'.$from_date.'" and cw_pay_structure.trans_status = "1" ORDER BY prime_pay_structure_id ASC LIMIT 0,1';
// and transaction_type = 1
$pay_str_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$pay_str_exist_qry')");
$pay_str_exist_rslt = $pay_str_exist_data->result();
$pay_str_exist_data->next_result();
$exist_pay_str_id = (int)$pay_str_exist_rslt[0]->prime_id;
if($exist_pay_str_id){
$error_info["error"]["$row"] = "Pay Structure Already Exist, Please Check Your From Date.!";
$count++;
}else{
//GET LAST PAY STRUCUTRE ID IN ARRAY FORMAT FOR UPDATE TO INACTIVE STATUS
//cw_pay_structure.category = "'.$category.'" and
$last_pay_str_qry = 'select cw_pay_structure.prime_pay_structure_id as prime_id from cw_pay_structure where '.$pay_str_where_qry.' cw_pay_structure.trans_status = "1" ORDER BY prime_pay_structure_id DESC LIMIT 0,1';
$last_pay_str_data = $this->db->query("CALL sp_a_run ('SELECT','$last_pay_str_qry')");
$last_pay_str_rslt = $last_pay_str_data->result();
$last_pay_str_data->next_result();
$last_pay_str_id = $last_pay_str_rslt[0]->prime_id;
if($last_pay_str_id){
$old_pay_id_arr[$row] = $last_pay_str_id;
}
}
}
//-------------------- EXCEL DUPLICATE CHECKING START ---------------------
$excel_exist_arr = array();
$excel_exist_arr = $this->add_array_keys_dynamic($excel_exist_arr,$excel_col_str_arr,$row);
if(count($excel_row_exist_arr) > 0){
$data_row_exist = '';
foreach($excel_row_exist_arr as $excel_row => $excel_data_arr){
//FUNCTION FOR VALUE EXIST CHECKING IN MULTIDIMENSIONAL ARRAY KEY BASED
$data_row_exist = $this->multi_dimens_arr_key_exist($excel_data_arr,$excel_col_str_arr);
if($data_row_exist){
break;
}
}
//EXIST ERROR
if($data_row_exist){
$error_info["error"]["$row"] = "Duplicate Row Present in Excel.!";
$count++;
}else{
$excel_row_exist_arr[$row] = $excel_exist_arr;
}
}else{
$excel_row_exist_arr[$row] = $excel_exist_arr;
}
//-------------------- EXCEL DUPLICATE CHECKING END ---------------------
}
}
}
}
}
//EXCEL ERRORS
if($count){
$table_info = $this->get_excel_error_ui($error_info);
echo json_encode(array('success'=>false,'message'=>"Excel Error",'table_info'=>$table_info));
exit(0);
}else{
// die;
for ($row = 1; $row <= $highestRow; $row++) {
$key_val_data = array();
$col_data = array();
for ($col = 0; $col < $highestColumnIndex; $col++) {
$data = $sheet->getCellByColumnAndRow($col, $row)->getValue();
//for date column
// || $col === 8
if($row === 1){
if($data === "From Date"){
$from_date_col = $col;
}
if($data === "To Date"){
$to_date_col = $col;
}
}
// $col === 9 || $col === 10
if(($col === $from_date_col || $col === $to_date_col) && $data !== null && $row !== 1){
$unix_date = ($data - 25569) * 86400;
$excel_date = 25569 + ($unix_date / 86400);
$unix_date = ($excel_date - 25569) * 86400;
$data = gmdate("Y-m-d", $unix_date);
}
$col_data[] = $data;
}
if($row === 1) {
// Header row. Save it in "$keys".
$key_data = $col_data;
}
// This is not the first row; so it is a data row.
if($row !== 1 && $key_data){
$key_val_data = array_combine($key_data, $col_data);
$pay_struct_qry_key = "";
$pay_struct_qry_val = "";
$pay_struct_id = "";
foreach($prime_table_col as $col_val){
$col_name = $col_val['col_name'];
$dis_col_name = ucwords(str_replace("_"," ",$col_name));
if($col_name === "tax_location"){
$col_name = "professional_tax_location";
}
//pay strucuture import settings module based array
/*--------------------THIS $pick_val_arr LINE DON'T DELETE-------------------------------------*/
// $pick_val_arr = $this->pay_import_arr[$col_name];
$pick_val_arr = $all_pick_import_arr[$col_name];
//picklist value get from array for insert a pay structure
$qry_val = $pick_val_arr[$key_val_data[$dis_col_name]];
//code for only actvity number picklist get based on wbs element
if($col_name === "wbs_element"){
$wbs_element = $key_val_data[$dis_col_name];
// $sap_proj = $sap_proj_rslt_arr[$wbs_element];
}
if($col_name === "activity_no"){
if($wbs_element){
$qry_val = $sap_act_no_arr[$wbs_element][$key_val_data[$dis_col_name]];
// if(!$get_key_val){
// $error_info[$row] = "Unknown ($get_cell_value) Data Mapped..!";
// }
}
// else{
// $error_info[$row] = "First You should Map a WBS Element Column After should Map a Activity Number Column..!";
// }
}
if($col_name === "professional_tax_location"){
$col_name = "tax_location";
}
if($qry_val){
//build a pay structure qry insert values
$pay_struct_qry_key .= $col_name.',';
$pay_struct_qry_val .= '"'.$qry_val.'",';
}else
if($dis_col_name === "Day Condition"){//build a pay structure qry insert values
$day_cond_val = $day_cond_arr[$key_val_data[$dis_col_name]];
$pay_struct_qry_key .= $col_name.',';
$pay_struct_qry_val .= '"'.$day_cond_val.'",';
}else
if($dis_col_name === "Pf Limit" || $dis_col_name === "Esi Limit"){//build a pay structure qry insert values
$status_mode_val = $status_mode_arr[strtoupper($key_val_data[$dis_col_name])];
if(!$status_mode_val){
$status_mode_val = 2;
}
$pay_struct_qry_key .= $col_name.',';
$pay_struct_qry_val .= '"'.$status_mode_val.'",';
}else
if($col_name === "from_date" || $col_name === "to_date" || $col_name === "effective_date" || $col_name === "day_count"){//build a pay structure qry insert values
//FOR OLD PAY STRUCTURE UPDATE
if($col_name === "from_date"){
$from_date = $key_val_data[$dis_col_name];
}
$pay_struct_qry_key .= $col_name.',';
$pay_struct_qry_val .= '"'.$key_val_data[$dis_col_name].'",';
}else
if($col_name === "status"){
$pay_status_val = $status_mode_arr[strtoupper($key_val_data[$dis_col_name])];
if(!$pay_status_val){
$pay_status_val = 1;
}
$pay_struct_qry_key .= $col_name.',';
$pay_struct_qry_val .= '"'.$pay_status_val.'",';
}else{
$pay_struct_qry_key .= $col_name.',';
if($col_name === "employee_type"){
if($key_val_data[$dis_col_name] === "Internal"){
$pay_struct_qry_val .= '"1",';
}else
if($key_val_data[$dis_col_name] === "External"){
$pay_struct_qry_val .= '"2",';
}else{
$pay_struct_qry_val .= '"2",';
}
}else{
$pay_struct_qry_val .= '"'.$key_val_data[$dis_col_name].'",';
}
}
}
if($pay_struct_qry_key && $pay_struct_qry_val){//build a pay structure qry insert values
$pay_struct_qry_key .= 'trans_created_by,trans_created_date';
$pay_struct_qry_val .= '"'.$logged_id.'","'.$today_date.'"';
//INSERT A PAY STRUCTURE QRY
$pay_struct_qry = 'INSERT INTO cw_pay_structure ('.$pay_struct_qry_key.') VALUES ('.$pay_struct_qry_val.')';
$pay_struct_info = $this->db->query("CALL sp_a_run ('INSERT','$pay_struct_qry')");
$pay_struct_rslt = $pay_struct_info->result();
$pay_struct_info->next_result();
$pay_struct_id = (int)$pay_struct_rslt[0]->ins_id;
if($pay_struct_id){
//OLD PAY STRUCTURE UPDATE(INACTIVE STATUS AND DATE)
if($old_pay_id_arr[$row]){
$old_pay_str_id = $old_pay_id_arr[$row];
//update qry for to date should update to last pay structure row data
if($old_pay_str_id < $pay_struct_id){
$last_to_date = date("Y-m-d",strtotime("-1 days",strtotime($from_date)));
$upd_last_pay_str_qry = 'UPDATE cw_pay_structure SET cw_pay_structure.to_date = "'.$last_to_date.'",cw_pay_structure.status = "2",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE cw_pay_structure.prime_pay_structure_id = "'.$old_pay_str_id.'" and cw_pay_structure.trans_status = 1';
$upd_last_pay_str_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_last_pay_str_qry')");
}
}
$pay_struct_line_key = "";
$pay_struct_line_val = "";
$ear_grs_amt = 0;
$wage_grs_amt = 0;
$pf_grs_amt = 0;
$esi_grs_amt = 0;
foreach($pay_struct_line_arr as $wage_key => $wage_arr){
// if($key_val_data[$wage_key." Amount"]){
$wage_name = $wage_arr['wage_name'];
$wage_code = $wage_arr['wage_code'];
$label_name = $wage_arr['label_name'];
if((int)$wage_arr['input_for'] === 34){
$field_type = "earnings";
}else
if((int)$wage_arr['input_for'] === 49){
$field_type = "deductions";
}
$amount = $key_val_data[$wage_key." Amount"];
//TOTAL EARNINGS GROSS AMOUNT CALCULATE
if($amount){
$ear_grs_amt += $amount;
}
//SELECTED WAGE GROSS(INCLUDE GROSS) AMOUNT CALCULATE
if($gross_wage_arr[$label_name]){
if($amount){
$wage_grs_amt += $amount;
}
}
$pf_applic = $status_mode_arr[strtoupper($key_val_data[$wage_key." PF Applicable"])];
if(!$pf_applic){
$pf_applic = 2;
}
$esi_applic = $status_mode_arr[strtoupper($key_val_data[$wage_key." ESI Applicable"])];
if(!$esi_applic){
$esi_applic = 2;
}
$pt_applic = $status_mode_arr[strtoupper($key_val_data[$wage_key." PT Applicable"])];
if(!$pt_applic){
$pt_applic = 2;
}
//DR CODE FOR PF AND ESI GROSS AMT CALCULATE 18OCT22 START
if((int)$pf_applic === 1){
$pf_grs_amt += $amount;
}
//if((int)$esi_applic === 1){
if($wage_map_arr[$label_name]){
$esi_grs_amt += $amount;
}
//}
//build a pay structure line qry values
$pay_struct_line_val .= '"'.$pay_struct_id.'","'.$field_type.'","'.$wage_code.'","'.$wage_name.'","'.$label_name.'","'.$amount.'","'.$pf_applic.'","'.$esi_applic.'","'.$pt_applic.'","'.$logged_id.'","'.$today_date.'"),(';
// }
}
if($pay_struct_line_val){
$pay_struct_line_val = rtrim($pay_struct_line_val,'),(');
//INSERT A PAY STRUCTURE LINE QRY
$pay_struct_line_key = 'prime_pay_structure_id,field_type,wage_code,wage_name,hrms_field_name,amount,pf_applicable,esi_applicable,pt_applicable,trans_created_by,trans_created_date';
$pay_struct_line_qry = 'INSERT INTO cw_pay_structure_line ('.$pay_struct_line_key.') VALUES ('.$pay_struct_line_val.')';
$pay_struct_line_info = $this->db->query("CALL sp_a_run ('INSERT','$pay_struct_line_qry')");
$pay_struct_line_rslt = $pay_struct_line_info->result();
$pay_struct_line_info->next_result();
//UPDATE QRY FOR UPDATE A TOTAL GROSS AMOUNT TO PAYSTRUCTURE TABLE
$upd_gross_amt_qry = 'UPDATE cw_pay_structure SET ear_gross = "'.$ear_grs_amt.'",gross = "'.$wage_grs_amt.'",pf_gross = "'.$pf_grs_amt.'",esi_gross = "'.$esi_grs_amt.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE cw_pay_structure.prime_pay_structure_id = "'.$pay_struct_id.'" and cw_pay_structure.trans_status = 1';
$upd_gross_amt_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_gross_amt_qry')");
if($pay_struct_line_rslt[0]){
$sts[$row] = "true";
}else{
$sts[$row] = "false";
}
}
}else{
$sts[$row] = "false";
}
}
}
}
if(in_array($sts,"false")){
echo json_encode(array('success' => FALSE, "message" => "Import Error.!"));
}else{
echo json_encode(array('success' => TRUE, 'message' => "Imported Successfully.!"));
}
}
}
/*-----------------------------------------------------------------------------------*/
public function day_cond_qry_struct_function(){
$day_cond_arr = array();
$select_columns = '*';
$table_name = 'cw_day_condition';
$table_join = '';
$table_where = 'cw_day_condition.trans_status = 1';
//FUNCTION USED FOR CREATE A QRY
$day_cond_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
/*$day_cond_arr = array_reduce($day_cond_rslt, function($result, $arr) {
$result[$arr['day_condition']] = $arr['prime_day_condition_id'];
return $result;
}, array());*/
$day_cond_arr = array();
foreach ($day_cond_rslt as $key => $value){
$day_cond_arr[$value['day_condition']] = $value['prime_day_condition_id'];
}
return $day_cond_arr;
}
public function status_mode_qry_struct_function(){
$day_cond_arr = array();
$select_columns = '*';
$table_name = 'cw_status_mode';
$table_join = '';
$table_where = 'cw_status_mode.trans_status = 1';
//FUNCTION USED FOR CREATE A QRY
$status_mode_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
/*$status_mode_arr = array_reduce($status_mode_rslt, function($result, $arr) {
$result[$arr['status_mode_value']] = $arr['prime_status_mode_id'];
return $result;
}, array());*/
$status_mode_arr = array();
foreach ($status_mode_rslt as $key => $value){
$status_mode_arr[$value['status_mode_value']] = $value['prime_status_mode_id'];
}
return $status_mode_arr;
}
//ANBU SIR PAY STRUCTURE IMPORT USING SP PROCEDURE
public function sp_excel_file_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);
}
$file_path = $this->input->post("file_path");
$db_name = $this->config->item("db_name");
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
try{
$excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($file_path);
}catch(Exception $e){
die('Error loading file "' . pathinfo($file_path, PATHINFO_BASENAME). '": ' . $e->getMessage());
return json_encode(array('success' => false, 'message' => "Invalid file or path"));
}
// $prime_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = 'cw_pay_structure' and COLUMN_NAME not like '%trans%' and COLUMN_NAME not in ('ear_gross','gross','pf_gross','esi_gross','effective_date','day_count')");
$prime_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = 'cw_pay_structure' AND COLUMN_NAME NOT IN ('trans_created_by','trans_created_date','trans_updated_by','trans_updated_date','trans_deleted_by','trans_deleted_date','trans_status','prime_pay_structure_id','ear_gross','gross','pf_gross','esi_gross','effective_date','day_count')");
// and COLUMN_NAME not like '%trans%'
$prime_table_col = $prime_table_query->result_array();
$pay_name_arr = array();
foreach($prime_table_col as $col_val){
// $col_name = $col_val['col_name'];
$show_name = ucwords(str_replace("_"," ",$col_val['col_name']));
$pay_name_arr[$show_name] = $show_name;
}
//function for get a pay structure details
// $get_pay_str_qry = 'select * from cw_pay_structure where cw_pay_structure.trans_status = 1';
// $get_pay_str_info = $this->db->query("CALL sp_a_run ('SELECT','$get_pay_str_qry')");
// $get_pay_str_rslt = $get_pay_str_info->result_array();
// $get_pay_str_info->next_result();
$pay_struct_line_qry = 'SELECT label_name,view_name,cw_wage_map_hrms.wage_name,cw_wage_map_hrms.wage_code,cw_form_setting.input_for FROM `cw_payroll_formula` INNER JOIN cw_form_setting on cw_form_setting.label_name = cw_payroll_formula.out_column inner join cw_wage_map_hrms on cw_wage_map_hrms.hrms_field_name = cw_form_setting.label_name WHERE cw_payroll_formula.order_by != 0 and cw_payroll_formula.formula_mode = 1 and cw_form_setting.paystructure_check = 1 and cw_payroll_formula.trans_status = 1 and cw_form_setting.trans_status = 1 ORDER BY order_by ASC';
$pay_struct_line_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_struct_line_qry')");
$pay_struct_line_rslt = $pay_struct_line_info->result_array();
$pay_struct_line_info->next_result();
$pay_line_name_arr = array();
$pay_struct_line_arr = array();
foreach($pay_struct_line_rslt as $line_val){
// $col_name = $col_val['col_name'];
$wage_name = ucwords(str_replace("_"," ",$line_val['wage_name']));
// $pay_line_name_arr[$wage_name] = $wage_name;
$pay_struct_line_arr[$wage_name] = $line_val;
}
//FOR GET PAY STRUCTURE LINE COLUMN
for($i = 0; $i <= 3; $i++){
if($i === 0){
$content = " Amount";
}else
if($i === 1){
$content = " PF Applicable";
}else
if($i === 2){
$content = " ESI Applicable";
}else
if($i === 3){
$content = " PT Applicable";
}
foreach($pay_struct_line_rslt as $line_val){
$wage_name = ucwords(str_replace("_"," ",$line_val['wage_name']));
$pay_line_name_arr[$wage_name.$content] = $wage_name.$content;
}
}
//TWO ARRAY CONVERT TO ONE ARRAY(GET PAY STRUCTURE AND LINE HEADER NAME)
$all_pay_name_arr = $pay_name_arr + $pay_line_name_arr;
$sheet = $excel_obj->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$highest_column = $sheet->getHighestColumn();
$worksheetTitle = $sheet->getTitle();
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highest_column);
$key_data = array();
$status_array = array();
$key_val_arr = array();
$sts = array();
$error_info = array();
$excel_line_col = array();
$pay_struct_val = "";
$count = 0;
$head_count = 0;
$last_row = 1;
$old_pay_id_arr = array();
$head_with_out_check_arr = array("SL NO","Active Status");
//FOR EXCEL DUPLICATE
$excel_row_exist_arr = array();
for ($row = 1; $row <= $highestRow; $row++) {
$key_val_data = array();
$col_data = array();
$status_info = array();
$status_info["Excel Row"] = $row;
$check_sts = 1;
$mi_where_qry = '';
$pay_str_where_qry = '';
$from_date_col = '';
$to_date_col = '';
for ($col = 0; $col < $highestColumnIndex; $col++) {
$data = $sheet->getCellByColumnAndRow($col, $row)->getValue();
//FOR GET COLUMN NAME LIKE A,B,C ETC
if($row === 1) {
// if($row === 1) {
// $data = strtolower(str_replace(" ","_",$data));
// }
// Header row. Save it in "$keys".
$excel_key = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col);
$excel_line_col[$data] = $excel_key;
if($data === "From Date"){
$from_date_col = $col;
}
if($data === "To Date"){
$to_date_col = $col;
}
//HEADER NAME ERROR VALIDATION CHECK
if(!in_array($data,$all_pay_name_arr) && !in_array($data,$head_with_out_check_arr)){
$error_info["error"]["$excel_key$row"] = "Invalid Header ($data) Mapped.!";
$head_count++;
}
}else{
//for date column
// || $col === 8
// $col === 9 || $col === 10
if(($col === $from_date_col || $col === $to_date_col) && ($data !== null || $data !=="") && $row !== 1){
$unix_date = ($data - 25569) * 86400;
$excel_date = 25569 + ($unix_date / 86400);
$unix_date = ($excel_date - 25569) * 86400;
$data = gmdate("Y-m-d", $unix_date);
}else
if(is_numeric($data)) {
$data = strval($data);
}
if($data === null){
$data = "";
}
}
$col_data[] = $data;
}
//EXCEL HEADER ERRORS
if($head_count){
$table_info = $this->get_excel_error_ui($error_info);
echo json_encode(array('success'=>false,'message'=>"Excel Error",'table_info'=>$table_info));
exit(0);
}else{
if($row === 1) {
// Header row. Save it in "$keys".
$key_data = $col_data;
}
// This is not the first row; so it is a data row.
// Transform $riga into a dictionary and add it to $data.
if($row !== 1 && $key_data){
$key_val_data = array_combine($key_data, $col_data);
$key_val_arr[$row] = $key_val_data;
$last_row += 1;
}
}
}
//JSON FORMAT CREATE CODE
$key_val_json = json_encode($key_val_arr);
// echo "last_row => $last_row , key_val_json::$key_val_json";die;
$save_data = $this->db->query("CALL itsp_vimport ('$key_val_json',$last_row)");
$save_rslt = $save_data->result_array();
$save_data->next_result();
$save_rslt_count = count($save_rslt);
// echo "<pre>";
// print_r($save_rslt);die;
// $row_num = (int)$save_rslt[0]['row_num'];
// $row_num = $save_rslt[0]->field_name;
// $row_num = $save_rslt[0]->row_num;
if($save_rslt_count){
$table_head = "<tr>";
$table_body = "<tr>";
foreach($save_rslt as $arr_key => $arr_val){
foreach($arr_val as $key => $val){
//FOR HEADER
if((int)$arr_key === 0){
$table_head .= "<th>$key</th>";
}
//FOR BODY
$table_body .= "<td>$val</td>";
}
//FOR HEADER
if((int)$arr_key === 0){
$table_head .= "</tr>";
}
//FOR BODY
$table_body .= "</tr>";
}
$table_data = "<table class='table table-bordered' id='table_details'><thead>$table_head</thead><tbody>$table_body</tbody></table>";
if($table_data){
echo json_encode(array('success' => TRUE, 'message' => "Pay Structure Imported Successfully.!",'table_info' => $table_data));
}else{
// $table_info = $this->get_excel_import_ui($save_rslt);
echo json_encode(array('success' => FALSE, "message" => "Import Error.!","table_info" => ''));
}
}
// $row_num = (int)$save_rslt[0]['row_num'];
// $row_num = $save_rslt[0]->field_name;
// $row_num = $save_rslt[0]->row_num;
}
/*-----------------------------------------------------------------------------------*/
// public function get_excel_import_rslt($status_array){
// $table_info = "";
// $th_line = "";
// $tr_line = "";
// $count = 0;
// $th_line = "<th style='text-align:center !important;'>Rows No</th>";
// $th_line .= "<th style='text-align:center !important;'>Error</th>";
// // }
// foreach($status_array as $status){
// $count++;
// // $status_array_count = count($status['num_rows']);
// $num_rows = $status['num_rows'];
// $field_name = str_replace('-',' ',$status['field_name']);
// $err_value = str_replace('-',' ',$status['err_value']);
// $status_count = 0;
// // $status_count++;
// // if((int)$count === 1){
// // $td_line .= "<td>$num_rows</td>";
// $color = "style='color:#15da15 !important;'";
// $tr_line .= "<tr $color><td>$num_rows</td></tr>";
// $td_line = "";
// }
// if($th_line !== ""){
// $table_info = "<table class='table table-bordered' style='text-align:center;'>
// <thead>
// <tr>
// $th_line
// </tr>
// </thead>
// <tbody>
// $tr_line
// </tbody>
// </table>";
// }
// return $table_info;
// }
//DR CODE START FOR PERSONAL AREA BASED PROFESSIONAL TAX SHOULD APPEND
public function tax_location_append(){
$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");
//select query for get a day count
$select_columns = 'cw_sap_professional_tax.ptax_code as ptax_code,cw_sap_professional_tax.ptax_name';
$table_name = 'cw_sap_professional_tax';
$table_join = '';
$table_where = 'cw_sap_professional_tax.trans_status = 1 and cw_sap_professional_tax.personal_code = "'.$personal_code.'"';
$pro_tax_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$pro_tax_list = "<option value = ''>---- Professional Tax List ----</option>";
$pro_tax_arr = array();
foreach($pro_tax_rslt as $for){
$ptax_code = $for['ptax_code'];
$ptax_name = $for['ptax_name'];
$pro_tax_list .= "<option value = '$ptax_code'>$ptax_name</option>";
$pro_tax_arr = $ptax_name;
}
// $data['pro_tax_list'] = $pro_tax_list;
// $data['pro_tax_arr'] = $pro_tax_arr;
echo json_encode(array('success' => TRUE, "message" => "", "pro_tax_list" => $pro_tax_list, "pro_tax_arr" => $pro_tax_arr));
}
//DR CODE END FOR PERSONAL AREA BASED PROFESSIONAL TAX SHOULD APPEND
//DUPLICATE ACTIVITY NO FOR SAME WBS FOUND IN ACTIVITY TABLE
public function duplicate_act_no(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$wbs_element = $this->input->post("wbs");
$activity_no = $this->input->post("act");
$activity_qry = 'SELECT * FROM cw_sap_activity WHERE trans_status = 1 AND act_wbs_id = "'.$wbs_element.'" AND act_no = "'.$activity_no.'"';
$activity_info = $this->db->query("CALL sp_a_run ('SELECT','$activity_qry')");
$activity_rslt = $activity_info->result();
$activity_info->next_result();
$act_count = count($activity_rslt);
echo $act_count;
}
public function prev_exempt_val_append(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$prime_id = (int)$this->input->post("prime_id");
if($prime_id > 0){
$exempt_sel_qry = 'select cw_pay_structure_line.wage_name,cw_pay_structure_line.hrms_field_name,cw_pay_structure_line.pf_applicable,cw_pay_structure_line.esi_applicable,cw_pay_structure_line.pt_applicable from cw_pay_structure_line where cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" and cw_pay_structure_line.trans_status = 1';
}else{
$exempt_sel_qry = 'select cw_wage_map_hrms.wage_name,cw_wage_map_hrms.hrms_field_name,cw_wage_map_hrms.pf_applicable,cw_wage_map_hrms.esi_applicable,cw_wage_map_hrms.pt_applicable from cw_wage_map_hrms where cw_wage_map_hrms.trans_status = 1';
}
$exempt_sel_info = $this->db->query("CALL sp_a_run ('SELECT','$exempt_sel_qry')");
$exempt_sel_rslt = $exempt_sel_info->result_array();
$exempt_sel_info->next_result();
$exempt_sel_arr = array();
foreach ($exempt_sel_rslt as $key => $value) {
$exempt_sel_arr[$value['hrms_field_name']] = $value;
}
/*$exempt_sel_arr = array_reduce($exempt_sel_rslt, function($result, $arr){
$result[$arr['hrms_field_name']] = $arr;
return $result;
}, array());*/
if($exempt_sel_rslt[0]){
echo json_encode(array('success' => TRUE, 'message' => "Proceed!", 'exempt_arr' => $exempt_sel_arr));
}else{
echo json_encode(array('success' => false, 'message' => "Please Map PF and ESI and PT Applicable Settings in Wage Map Hrms Module.!"));
}
}
//DR CODE FOR WBS ELEMENT FETCH BASED ON PERSONAL AREA
public function get_wbs(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$search_term = $this->input->post_get('term');
$personal_code = $this->input->post('personal_code');
$search_info = $this->db->query("CALL sp_sap_picks ('$search_term','$personal_code','wbs_element')");
$search_rslt = $search_info->result();
$search_info->next_result();
if($search_rslt[0]){
echo json_encode(array('success' => true, 'search_rslt' => $search_rslt));
}else{
echo json_encode(array('success' => false, 'message' => "No wbs Found for this personal area..!"));
}
}
public function get_pick_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$wbs_element = $this->input->post("wbs_element");
$pro_qry[] = array("return"=>"activity_info","qry"=>'SELECT cw_sap_activity.prime_sap_activity_id,cw_sap_activity.act_no,cw_sap_activity.act_desc FROM cw_sap_activity WHERE cw_sap_activity.act_wbs_id = "'.$wbs_element.'" and cw_sap_activity.trans_status = 1');
$pro_qry[] = array("return"=>"network_info","qry"=>'select cw_sap_wbs.wbs_id,cw_sap_wbs.wbs_network_id from cw_sap_wbs where trans_status = 1 and cw_sap_wbs.wbs_id = "'.$wbs_element.'"');
$pro_qry[] = array("return"=>"position_info","qry"=>'select position_code,position_name from cw_sap_position where trans_status = 1');
$pick_data_rslt = $this->run_multi_qry($pro_qry);
if(!$pick_data_rslt->sts){
echo json_encode(array('success' => false, 'message' => "Please try After Sometime..!"));
}else{
$activity_rslt = $pick_data_rslt->rslt->activity_info;
$network_rslt = $pick_data_rslt->rslt->network_info;
$position_rslt = $pick_data_rslt->rslt->position_info;
echo json_encode(array('success' => true, 'activity_rslt' => $activity_rslt, 'network_rslt' => $network_rslt, 'position_rslt' => $position_rslt));
}
}
}
?>