File: //home/cafsindia/uds.cafsinfotech.in/application/controllers/Time_sheet.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 Time_sheet extends Action_controller{
public function __construct(){
parent::__construct('time_sheet');
//ASSIGN TIME SHEET AS MI FMS TABLE(FOR BOTH AS SAME TABLE)
$this->prime_table = "cw_monthly_input_fms";
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//VIEW INFO FUNCTION
$this->cats_page_info();
$max_count = count($this->table_head ?? []) - 1;
$this->table_head[$max_count]->label_name = 'position_name';
$this->table_head[$max_count]->view_name = 'Position Description';
$this->table_head[$max_count]->field_type = 1;
$this->table_head[$max_count]->label_name = 'gross';
$this->table_head[$max_count]->view_name = 'Gross';
$this->table_head[$max_count]->field_type = 2;
$data['table_head'] = $this->table_head;
$data['encKey'] = $this->generateKey();
//$data['table_head']['gross'] = '';
$data['all_pick'] = $this->pick_list;
//$data['export_pick'] = $this->export_pick_arr;
//PERSONAL AREAR ACCESS ONLY EXCEPT SUPERADMIN USER
$area_control_where = '';
if((int)$this->logged_user_role !== 1){
$area_control_where = 'and cw_sap_personal_area.personal_code in ('.$this->logged_area_access.') ';
}
$data['per_area_list'] = $data['all_pick']['personal_code']['array_list'];
$data['project_list'] = $data['all_pick']['project_id']['array_list'];
$data['module_id'] = $this->control_name;
$this->load->view('time_sheet/manage', $data);
}
//CHECK MONTHLY INPUT LOCKED OR NOT
public function check_time_sheet(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$search_month = $this->input->post('search_month');
if($search_month){
//Check Lock Month
$exist_qry = 'select lock_month from cw_monthly_input_lock where lock_month = "' . $search_month . '" and status = 1 and trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$num_rows = $exist_data->num_rows();
$exist_data->next_result();
if ((int) $num_rows > 0) {
echo json_encode(array('success' => false,'status' => "locked",'message' => "Monthly Input Locked For this Month!!!"
));
exit(0);
}else{
echo json_encode(array('success' => true,'message' => "Proceed..!"));
}
}
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
public function search(){
$dec_data = $this->cryptoDecrypt($_POST['encrypted_data']);
$_POST = $dec_data['data'];
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..'));
exit(0);
}
//SEARCH INFO FUNCTION
$this->search_info();
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$order = $this->input->post('order');
$order_col = $this->input->post('columns');
$search = $this->input->post('search');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$order_col = $order_col[$column]['data'];
$search = trim($search['value']);
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
//TIME SHEET INSERT OR UPDATE SELECT FORMAT
$this->base_query = str_replace("cw_time_sheet","cw_monthly_input_fms",$this->base_query);
// echo $this->select_query;die;
$search_query = str_replace("@SELECT@",$this->select_query,$this->base_query);
$search_query = str_replace("cw_time_sheet","cw_monthly_input_fms",$search_query);
$search_query = str_replace("prime_time_sheet_id","prime_monthly_input_fms_id",$search_query);
$mi_select_qry = str_replace("select ","",$search_query);
$mi_select_qry = str_replace("cw_monthly_input_fms.","",$mi_select_qry);
$mi_select_qry = str_replace("prime_monthly_input_fms_id,","",$mi_select_qry);
$mi_select_qry = str_replace(",md","",$mi_select_qry);
$mi_select_qry = str_replace(",process_month","",$mi_select_qry);
$mi_emp_select_qry = str_replace(" from cw_monthly_input_fms","",$mi_select_qry);
$mi_ins_select_qry = str_replace(",personal_code",",cw_employees.personal_code",$mi_emp_select_qry);
$mi_ins_select_qry = str_replace(",wbs_element",",cw_employees.wbs_element",$mi_ins_select_qry);
$mi_ins_select_qry = str_replace(",position",",cw_employees.position",$mi_ins_select_qry);
$mi_ins_select_qry = str_replace(",activity_no",",cw_employees.activity_no",$mi_ins_select_qry);
//AFTER INSERT OR UPDATE TIME SHEET SELECT QRY FORMAT
//for select qry
$search_query = str_replace(",",",cw_monthly_input_fms.",$search_query);
$search_query = str_replace(",cw_monthly_input_fms.process_month","",$search_query);
$search_query = str_replace(",cw_monthly_input_fms.role","",$search_query);
// $search_query = str_replace(",cw_monthly_input_fms.emp_name","",$search_query);
$search_query = str_replace(",cw_monthly_input_fms.termination_status","",$search_query);
$search_query = str_replace(",cw_monthly_input_fms.date_of_joining","",$search_query);
$search_query = str_replace(",cw_monthly_input_fms.date_of_birth","",$search_query);
$search_query = str_replace(",cw_monthly_input_fms.sub_date","",$search_query);
$search_query = str_replace(",cw_monthly_input_fms.chk_date","",$search_query);
$search_query = str_replace(",cw_monthly_input_fms.msal_apr_date","",$search_query);
$search_query = str_replace(",cw_monthly_input_fms.msal_rej_date","",$search_query);
$search_query = str_replace(" from",",cw_monthly_input_fms.entry_status,cw_monthly_input_fms.check_status,cw_monthly_input_fms.payroll,cw_monthly_input_fms.remarks,cw_sap_position.position_name,cw_pay_structure.gross from",$search_query);
$table = $this->input->post('table');
$action = $this->input->post('action');
$search_month = $this->input->post('search_month');
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$month_days = $this->input->post('month_days');
$filter_wbs_arr = $this->input->post('filter_wbs');
if($filter_wbs_arr === null || $filter_wbs_arr === ""){
$filter_wbs_arr = [];
}
$filter_wbs_str = implode(",",$filter_wbs_arr ?? []);
$filter_wbs = str_replace(',','","',$filter_wbs_str);
$this->prime_table = "cw_monthly_input_fms";
$filter_qry = "";
$start_date = date("Y-m-t",strtotime('01-'.$search_month));
$end_date = date("Y-m-d",strtotime('01-'.$search_month));
$msg = "";
//FUNCTION FOR GET A PAY STRUCUTRE DATA BASED ON PERSONAL AREA AND PROJECT ID
$pay_struct_rslt = $this->pay_structure_qry_fun($search_month,$personal_code,$project,$start_date,$end_date);
$grp_wbs = str_replace(',','","',$pay_struct_rslt[0]->wbs_element);
$grp_position = str_replace(',','","',$pay_struct_rslt[0]->position);
//$grp_act_no = str_replace(',','","',$pay_struct_rslt[0]->activity_no); and cw_employees.activity_no in ("'.$grp_act_no.'") removed as discussed with porul 15jun2023
$created_on = date("Y-m-d H:i:s");
//TIME SHEET INSERT CONDITION
if($action === "save" && $table === "input_table"){
// && $month_days
if($search_month && $personal_code && $project) {
if($grp_wbs && $grp_position){
//Delete monthy input 0 Records when New wbs element coming in Employee master
$remove_qry = 'DELETE mi FROM cw_monthly_input_fms mi LEFT JOIN cw_employees emp ON mi.employee_code = emp.employee_code AND mi.wbs_element = emp.wbs_element
WHERE emp.wbs_element IS NULL AND mi.trans_status = 1 AND mi.process_month = "'.$search_month.'" AND mi.personal_code = "'.$personal_code.'" AND mi.project_id = "'.$project.'" AND mi.entry_status = 0';
$this->db->query("CALL sp_a_run ('RUN','$remove_qry')");
//Check monthy input data exist Start
$month_input_exist_qry = 'select employee_code from cw_monthly_input_fms where cw_monthly_input_fms.trans_status = 1 and process_month = "'.$search_month.'" and cw_monthly_input_fms.personal_code = "'.$personal_code.'" and cw_monthly_input_fms.project_id = "'.$project.'" limit 0,1';
$month_input_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$month_input_exist_qry')");
$month_input_exist_rslt = $month_input_exist_data->result();
$month_input_exist_data->next_result();
$month_input_exist_count = (int)$month_input_exist_data->num_rows();
/* inner join cw_sap_activity on cw_employees.activity_no = cw_sap_activity.prime_sap_activity_id and cw_sap_activity.act_start_date <= "'.$start_date.'" and cw_sap_activity.act_end_date >= "'.$end_date.'"
Check monthy input data exist END
Zero Entry in monthly input then insert data Start*/
//and cw_pay_structure.category = cw_employees.role ss
if ($month_input_exist_count === 0){
$save_month_fms_qry = 'INSERT INTO cw_monthly_input_fms(employees_id,'.$mi_emp_select_qry.',process_month,payroll,entry_status,check_status,payroll_status,md,trans_created_by,trans_created_date) SELECT prime_employees_id,'.$mi_ins_select_qry.',"'.$search_month.'",IF((emp_cost_center_code = "" or emp_cost_center_code = 0),1,0),0,0,2,"'.$month_days.'","'.$logged_id.'","'.$today_date.'" FROM cw_employees inner join cw_pay_structure on (cw_pay_structure.personal_code = cw_employees.personal_code and cw_pay_structure.wbs_element = cw_employees.wbs_element and cw_pay_structure.position = cw_employees.position and date_format(cw_pay_structure.from_date, "%Y-%m-%d") <= "'.$start_date.'" and date_format(cw_pay_structure.to_date, "%Y-%m-%d") >= "'.$end_date.'" and cw_pay_structure.trans_status = 1) inner join cw_sap_activity on cw_pay_structure.activity_no = cw_sap_activity.prime_sap_activity_id and cw_sap_activity.act_start_date <= "'.$start_date.'" and cw_sap_activity.act_end_date >= "'.$end_date.'" WHERE cw_employees.trans_status = 1 and cw_employees.personal_code = "'.$personal_code.'" and cw_employees.project_id = "'.$project.'" and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m-%d") <= "'.$start_date.'" and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, "%Y-%m-%d") >= "'.$end_date.'")) and cw_employees.entry_flag = "e" and cw_employees.sap_status = 5';
// and cw_employees.sap_status = 5
$save_month_fms_info = $this->db->query("CALL sp_a_run ('INSERT','$save_month_fms_qry')");
$save_month_fms_rslt = $save_month_fms_info->result();
$save_month_fms_info->next_result();
}else{
$info = $this->db->query("CALL itsp_time_sheet ('$personal_code','$project','$search_month','$month_days','".$this->logged_id."')");
$info->result();
$info->next_result();
}
}else{
$msg = "No data Avilable.!Please Check Wbs,Position,Start and Finish Date Conditions.!";
}
}
}
//FOR FILTER BASED DATA GET WHERE QRY
if($filter_wbs){
$filter_qry = 'and '.$this->prime_table.'.wbs_element in ("'.$filter_wbs.'")';
}
$common_search = "";
if($search){
$common_search .= ' and (cw_monthly_input_fms.role like "'.$search.'%" or cw_monthly_input_fms.personal_code like "'.$search.'%" or cw_monthly_input_fms.project_id like "'.$search.'%" or cw_monthly_input_fms.wbs_element like "'.$search.'%" or cw_monthly_input_fms.position like "'.$search.'%" or cw_monthly_input_fms.activity_no like "'.$search.'%" or cw_monthly_input_fms.employee_code like "'.$search.'%" or cw_monthly_input_fms.emp_name like "'.$search.'%")';
}
//MAKER AND CHECKER BASED SELECT QRY BUILD
$tab_base_whr_qry = '';
if($table === "input_table"){ //MAKER TABLE SELECT QRY
$tab_base_whr_qry = ' and '.$this->prime_table.'.entry_status in (0,2)';
}else //CHECKER TABLE SELECT QRY
if($table === "checker_table"){
$tab_base_whr_qry = ' and '.$this->prime_table.'.entry_status in (1)';
}
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query .= " inner join cw_employees on cw_employees.employee_code = $this->prime_table.employee_code inner join cw_sap_activity on $this->prime_table.activity_no = cw_sap_activity.prime_sap_activity_id and cw_sap_activity.act_start_date <= \"".$start_date."\" and cw_sap_activity.act_end_date >= \"".$end_date."\" where $this->prime_table.trans_status = 1 and $this->prime_table.process_month =\"".$search_month."\" ".$filter_qry.$common_search.$tab_base_whr_qry." and $this->prime_table.personal_code = \"".$personal_code."\" and $this->prime_table.project_id = \"".$project."\" and $this->prime_table.wbs_element in (\"".$grp_wbs."\") and $this->prime_table.position in (\"".$grp_position."\") and cw_employees.date_of_joining <= \"".$start_date."\" and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and cw_employees.resignation_date >= \"".$end_date."\")) and cw_employees.entry_flag = 'e' order by $this->prime_table.$order_col $order_sor";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " inner join cw_employees on cw_employees.employee_code = $this->prime_table.employee_code inner join cw_sap_activity on $this->prime_table.activity_no = cw_sap_activity.prime_sap_activity_id and cw_sap_activity.act_start_date <= \"".$start_date."\" and cw_sap_activity.act_end_date >= \"".$end_date."\" inner join cw_sap_position on cw_sap_position.position_code = $this->prime_table.position inner join cw_pay_structure on (cw_pay_structure.personal_code = $this->prime_table.personal_code and cw_pay_structure.wbs_element = $this->prime_table.wbs_element and cw_pay_structure.position = $this->prime_table.position and cw_pay_structure.category = $this->prime_table.role and date_format(cw_pay_structure.from_date, '%Y-%m') <= date_format(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m') and date_format(cw_pay_structure.to_date, '%Y-%m') >= date_format(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m')) where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" ".$filter_qry. $common_search.$tab_base_whr_qry." and $this->prime_table.personal_code = \"".$personal_code."\" and $this->prime_table.project_id = \"".$project."\" and $this->prime_table.wbs_element in (\"".$grp_wbs."\") and $this->prime_table.position in (\"".$grp_position."\") and cw_pay_structure.trans_status = 1 and cw_employees.date_of_joining <= \"".$start_date."\" and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and cw_employees.resignation_date >= \"".$end_date."\")) and cw_employees.entry_flag = 'e' order by $this->prime_table.$order_col $order_sor";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
//echo $search_query; die;
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
if($msg){
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $num_rows,"recordsFiltered" => $filtered_count,"data" => $search_result,"error" => $msg,));
}else{
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $num_rows,"recordsFiltered" => $filtered_count,"data" => $search_result,"message" => $msg,));
}
}
//PAY STRUCTURE QRY FUNCTION
public function pay_structure_qry_fun($search_month,$personal_code,$project,$start_date,$end_date){
//inner join cw_sap_activity on cw_pay_structure.activity_no = cw_sap_activity.prime_sap_activity_id and cw_sap_activity.act_start_date <= "'.$start_date.'" and cw_sap_activity.act_end_date >= "'.$end_date.'" As discussed with porul its removed
$pay_struct_qry = 'select GROUP_CONCAT(DISTINCT cw_pay_structure.wbs_element) as wbs_element,GROUP_CONCAT(DISTINCT cw_pay_structure.position) as position,GROUP_CONCAT(DISTINCT cw_pay_structure.activity_no) as activity_no from cw_pay_structure inner join cw_sap_wbs on cw_pay_structure.wbs_element = cw_sap_wbs.wbs_id where cw_pay_structure.trans_status = 1 and date_format(cw_pay_structure.from_date, "%Y-%m") <= date_format(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and date_format(cw_pay_structure.to_date, "%Y-%m") >= date_format(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and cw_pay_structure.personal_code = "'.$personal_code.'" and cw_sap_wbs.wbs_project_id = "'.$project.'" and cw_sap_wbs.wbs_start_date <= "'.$start_date.'" and cw_sap_wbs.wbs_end_date >= "'.$end_date.'"';
$pay_struct_data = $this->db->query("CALL sp_a_run ('SELECT','$pay_struct_qry')");
$pay_struct_rslt = $pay_struct_data->result();
$pay_struct_data->next_result();
return $pay_struct_rslt;
}
public function update_table(){
$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);
}
$label_name = $this->input->post('label_name');
$value = $this->input->post('value');
$prime_id = $this->input->post('prime_id');
$process_month = $this->input->post('hid_process_month');
$hid_process_month = date("Y-m-d", strtotime("01-" . $this->input->post('hid_process_month')));
$employee_code = $this->input->post('hid_emp_code');
$tab_name = $this->input->post('tab_name');
$payroll = (int)$this->input->post('payroll');
$upd_hold_reason = $this->input->post('upd_hold_reason');
$value = $this->input->post('value');
// $check_pd = (float)$this->input->post('check_pd');
// $check_buffer_pd = (float)$this->input->post('check_buffer_pd');
$check_pd = number_format((float)$this->input->post('check_pd'), 2, '.', '');
$check_buffer_pd = number_format((float)$this->input->post('check_buffer_pd'), 2, '.', '');
$this->prime_table = "cw_monthly_input_fms";
$this->prime_id = "prime_monthly_input_fms_id";
//EXIST CHECK CONDITION
if($prime_id){
$month_input_qry = 'select count(prime_monthly_input_fms_id) as count from '.$this->prime_table.' where '.$this->prime_table.'.trans_status = 1 and '.$this->prime_table.'.process_month = "' . $process_month . '" and '.$this->prime_table.'.payroll_status in (0,2) and '.$this->prime_table.'.entry_status = 1 and '.$this->prime_table.'.prime_monthly_input_fms_id = "'.$prime_id.'"';
$month_input_data = $this->db->query("CALL sp_a_run ('SELECT','$month_input_qry')");
$month_input_rslt = $month_input_data->result();
$month_input_data->next_result();
if((int)$month_input_rslt[0]->count){
echo json_encode(array("success" => FALSE,'message' => 'Could not Update..!Because Maker Status Already Approved..!'));
exit(0);
}else{
$created_on = date("Y-m-d H:i:s");
$upd_data = '';
$emp_data = '';
//PAID DAYS AND BUFFER DAYS BASED
if($label_name === "pd" || $label_name === "buffer_pd"){
if($value === "0"){
$upd_data = ',entry_status = 0,check_status = 0,trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'"';
}else{
$upd_data = ',entry_status = 2,check_status = 0,trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'"';
}
}else{
if($check_pd === "0.00" && $check_buffer_pd === "0.00"){
$upd_data = ',entry_status = 0,check_status = 0,trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'"';
}else{
$upd_data = ',entry_status = 2,check_status = 0,trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'"';
}
}
if($upd_hold_reason === "YES"){
$upd_data .= ',hold_reason = ""';
$emp_data = ',hold_reason = ""';
}
if($label_name !== "stop_pay_code" && $label_name !== "hold_reason"){
$upd_query = 'UPDATE ' . $this->prime_table . ' SET ' . $label_name . ' = "' . $value . '"'.$upd_data.' WHERE ' . $this->prime_id . ' = "' . $prime_id . '"';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
}else{
$upd_query = 'UPDATE '.$this->prime_table.' SET ' . $label_name . ' = "' . $value . '",trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'"'.$emp_data.' WHERE ' . $this->prime_id . ' = "' . $prime_id . '"';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
}
//EMPLOYEE MASTER UPD QRY FOR PAYMENT HOLD STATUS AND REMARKS WAS UPDATE
if($label_name === 'stop_pay_code' || $label_name === 'hold_reason'){
//GET PREV DATA
IF($label_name === 'stop_pay_code'){
$prev_data_qry = 'select prime_employees_id,emp_name,stop_pay_code from cw_employees where employee_code = "'.$employee_code.'"';
$prev_data = $this->db->query("CALL sp_a_run ('SELECT','$prev_data_qry')");
$prev_data_rslt = $prev_data->result();
$prev_data->next_result();
}
$emp_upd_query = 'UPDATE cw_employees SET ' . $label_name . ' = "' . $value . '",trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'"'.$emp_data.' WHERE cw_employees.employee_code = "'.$employee_code.'"';
$this->db->query("CALL sp_a_run ('UPDATE','$emp_upd_query')");
//INSERT INTO EMPLOYEE LOG AND MIDDLEWARE
if($prev_data_rslt){
$emp_name = $prev_data_rslt[0]->emp_name;
$prime_employees_id = $prev_data_rslt[0]->prime_employees_id;
$prev_stop_pay_code = $prev_data_rslt[0]->stop_pay_code;
//INSERT INTO EMPLOYEE LOG
$sap_post_qry = 'INSERT INTO cw_employees_log(prime_employees_id,employee_code,emp_name,label_name,old_value,new_value,trans_created_by,trans_created_date) VALUES("'.$prime_employees_id.'","'.$employee_code.'","'.$emp_name.'","'.$label_name.'","'.$prev_stop_pay_code.'","'.$value.'","'.$this->logged_id.'","'.$created_on.'")';
$stop_pay_res = $this->db->query("CALL sp_a_run ('RUN','$sap_post_qry')");
if($stop_pay_res){
$sap_post_qry = 'INSERT INTO uds_middleware.cw_employees_update(employee_code,stop_pay_code) VALUES("'.$employee_code.'","'.$value.'")';
$this->db->query("CALL sp_a_run ('RUN','$sap_post_qry')");
}
}
}
echo json_encode(array('success' => true,'message' => "Updated successfully!!!"));
}
}
}
//DR CODE START FOR SAVE ENTRY(MAKER) STATUS TO MI FMS TABLE 31AUG22
public function save_entry_status(){
$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);
}
$created_on = date('Y-m-d H:i:s');
$table_data = json_decode($this->input->post('table_data'), true);
$project = $this->input->post('project');
$process_month = $this->input->post('process_month');
$personal_code = $this->input->post('personal_code');
$filter_wbs_str = implode(",",$this->input->post('filter_wbs') ?? []);
$filter_wbs = str_replace(',','","',$filter_wbs_str);
$filter_qry = "";
//IF WE FILTER A WBS THEN WE SHOULD ADD A WBS WHERE QRY
if($filter_wbs){
$filter_qry = 'and '.$this->prime_table.'.wbs_element in ("'.$filter_wbs.'")';
}
$first_index = reset($table_data);
$keys = array_keys($first_index ?? []);
//TABLE DATA UPDATE
foreach($table_data as $mi_id => $mi_arr){
$update_query = '(';
foreach($mi_arr as $label_name => $label_value){
if($label_name === "stop_pay_code" && $label_value === "on"){
$label_value = "N";
}
$update_query .= '"' . $label_value . '",';
}
$update_query = rtrim($update_query, ', ');
$update_query .= ')';
$update_queries[] = $update_query;
}
$mi_data_upd_value = implode(", ", $update_queries ?? []);
$insert_query = "";
$update_query = "";
foreach($keys as $key){
if($key === "prime_id"){
$key = "prime_monthly_input_fms_id";
}
$insert_query .= $key . ",";
$update_query .= $key . "= VALUES(" . $key ."),";
}
$insert_query = rtrim($insert_query, ",");
$update_query = rtrim($update_query, ",");
if($mi_data_upd_value){
$upd_qry = 'INSERT INTO cw_monthly_input_fms('.$insert_query.') VALUES '.$mi_data_upd_value.' ON DUPLICATE KEY UPDATE '.$update_query.',trans_updated_by = VALUES(trans_updated_by),trans_updated_date = VALUES(trans_updated_date)';
$upd_info = $this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
}
//UPDATE CHECK,ENTRY STATUS
$select_upd_qry = 'SELECT COUNT(*) as count FROM cw_monthly_input_fms WHERE (cw_monthly_input_fms.pd != 0.00 or cw_monthly_input_fms.buffer_pd != 0.00) and cw_monthly_input_fms.entry_status = "2" and cw_monthly_input_fms.payroll_status = "2" and cw_monthly_input_fms.personal_code = "'.$personal_code.'" and cw_monthly_input_fms.project_id = "'.$project.'" and cw_monthly_input_fms.process_month = "'.$process_month.'"'.$filter_qry.' and cw_monthly_input_fms.trans_status = 1';
$select_upd_data = $this->db->query("CALL sp_a_run ('SELECT','$select_upd_qry')");
$select_upd_rslt = $select_upd_data->result();
$select_upd_data->next_result();
$upd_count = (int)$select_upd_rslt[0]->count;
if($upd_count === 0){
echo json_encode(array('success' => FALSE,'message' => 'No Data Available...!'));
}else{
//UPDATE QRY BASED ON ENTRY(MAKER) STATUS 2 TO ENTRY(MAKER) STATUS SHOULD 1
$upd_fms_query = 'UPDATE cw_monthly_input_fms SET cw_monthly_input_fms.entry_status = "1",cw_monthly_input_fms.check_status = "0",cw_monthly_input_fms.trans_updated_by = "'.$this->logged_id.'",cw_monthly_input_fms.trans_updated_date = "'.$created_on.'",cw_monthly_input_fms.sub_date = "'.$created_on.'" WHERE (cw_monthly_input_fms.pd != 0.00 or cw_monthly_input_fms.buffer_pd != 0.00) and cw_monthly_input_fms.pd <= cw_monthly_input_fms.md and cw_monthly_input_fms.entry_status = "2" and cw_monthly_input_fms.payroll_status = "2" and cw_monthly_input_fms.personal_code = "'.$personal_code.'" and cw_monthly_input_fms.project_id = "'.$project.'" and cw_monthly_input_fms.process_month = "'.$process_month.'"'.$filter_qry.' and cw_monthly_input_fms.trans_status = 1 and cw_monthly_input_fms.check_status <> 1';
$upd_fms_info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_fms_query')");
//Update as 0 for 0 paid days cats when revert paid days to zero
//,cw_pre_audit_salary.trans_status=0
$pre_upd_query = 'UPDATE cw_pre_audit_salary inner join cw_monthly_input_fms on cw_monthly_input_fms.prime_monthly_input_fms_id = cw_pre_audit_salary.prime_monthly_input_fms_id SET audit_status= 0,cw_pre_audit_salary.trans_status=0,sal_audit_status=0,deploy_audit_status=0,mandays_audit_status=0,allowance_audit_status=0,cw_pre_audit_salary.trans_updated_by = "'.$this->logged_id.'",cw_pre_audit_salary.trans_updated_date = "'.$created_on.'" WHERE cw_monthly_input_fms.pd = 0.00 and cw_monthly_input_fms.buffer_pd = 0.00 and cw_monthly_input_fms.personal_code = "'.$personal_code.'" and cw_monthly_input_fms.project_id = "'.$project.'" and cw_monthly_input_fms.process_month = "'.$process_month.'"'.$filter_qry.' and cw_monthly_input_fms.trans_status = 1';
$this->db->query("CALL sp_a_run ('UPDATE','$pre_upd_query')");
echo json_encode(array('success' => TRUE,'message' => 'Successfully Saved...!'));
}
}
//DR CODE END FOR SAVE ENTRY(MAKER) STATUS TO MI FMS TABLE 31AUG22
//UPDATE STATUS TO DELETE IN MODULE PRIMARY TABLE
public function delete(){
$delete_ids = implode(",",$this->input->post('delete_ids') ?? []);
$can_process = TRUE;
$delete_status = FALSE;
if($this->check_delete_status()){
$delete_status = TRUE;
$check_table_query = 'SELECT GROUP_CONCAT(prime_module_id) as prime_module_id,GROUP_CONCAT(label_name) as label_name from cw_form_setting WHERE pick_table = "'. $this->prime_table .'" and trans_status = 1 ';
$check_table_info = $this->db->query("CALL sp_a_run ('SELECT','$check_table_query')");
$check_table_rlst = $check_table_info->row();
$check_table_info->next_result();
if($check_table_rlst->prime_module_id){
$prime_module_id = explode(",",$check_table_rlst->prime_module_id ?? "");
$label_name = explode(",",$check_table_rlst->label_name ?? "");
$i = 0;
$select_table = '';
$select_label = '';
$select_trans_status = '';
$select_where = '';
foreach($prime_module_id as $check_modules){
$table_name = "cw_".$check_modules;
$table_rename = $table_name."_$i";
$select_table .= "$table_rename.$label_name[$i],";
$select_label .= " $table_name $table_rename,";
if((int)$i === 0){
$select_trans_status .= "( $table_rename.trans_status = 1";
$select_where .= " and ($table_rename.$label_name[$i] in ($delete_ids)";
}else{
$select_trans_status .= " and $table_rename.trans_status = 1";
$select_where .= " or $table_rename.$label_name[$i] in ($delete_ids)";
}
$i++;
}
$select_trans_status .= ")";
$select_where .= ")";
$select_table = rtrim($select_table,',');
$select_label = rtrim($select_label,',');
$check_module_query .= 'SELECT '.$select_table.' from '.$select_label.' WHERE '.$select_trans_status.' '.$select_where.' LIMIT 0,1';
$check_module_info = $this->db->query("CALL sp_a_run ('SELECT','$check_module_query')");
$values_count = $check_module_info->num_rows();
$check_module_info->next_result();
if((int)$values_count > 0){
$can_process = False;
$delete_status = False;
}
}
if($delete_status){
$delete_query = 'DELETE FROM '. $this->prime_table .' WHERE '. $this->prime_id .' in ('. $delete_ids .')';
if($this->db->query("CALL sp_a_run ('RUN','$delete_query')")){
$row_set_query = 'SELECT form_view_label_name from cw_form_view_setting where form_view_type = "3" and prime_view_module_id = "'. $this->control_name .'" and trans_status = 1';
$row_set_info = $this->db->query("CALL sp_a_run ('SELECT','$row_set_query')");
$row_count = (int)$row_set_info->num_rows();
$row_set_info->next_result();
if($row_count !== 0){
$row_set_result = $row_set_info->result();
$delete_table_name = '';
$delete_table_condition = '';
foreach($row_set_result as $row_set){
$row_set_table_name = "cw_".$this->control_name."_".$row_set->form_view_label_name;
$delete_table_name .= "$row_set_table_name,";
$delete_table_condition .= " $row_set_table_name.$this->prime_id in ('$delete_ids') and";
}
$delete_table_name = rtrim($delete_table_name,',');
$delete_table_condition = rtrim($delete_table_condition,'and');
$delete_row_set_query = 'DELETE FROM '. $delete_table_name .' WHERE '. $delete_table_condition.'';
$this->db->query("CALL sp_a_run ('RUN','$delete_row_set_query')");
}
$can_process = False;
}
}
}
if($can_process){
$created_on = date("Y-m-d H:i:s");
$prime_upd_query .= 'trans_deleted_by = "'. $this->logged_id .'",trans_deleted_date = "'.$created_on.'"';
$prime_update_query = 'UPDATE '. $this->prime_table .' SET trans_status = 0,'. $prime_upd_query .' WHERE '. $this->prime_id .' in ('. $delete_ids .')';
if($this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')")){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to delete"));
}
}else
if($delete_status){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}else{
$modules = ucwords($check_table_rlst->prime_module_id);
echo json_encode(array('success' => FALSE, 'message' => "Unable to delete, This value is already used in $modules modules"));
}
}
//CHECK UNIQUE FIELD STATUS
public function check_delete_status(){
$check_delete_query = 'SELECT GROUP_CONCAT(unique_field) as unique_field from cw_form_setting WHERE prime_module_id = "'. $this->control_name .'" and trans_status = 1 ';
$check_delete_info = $this->db->query("CALL sp_a_run ('SELECT','$check_delete_query')");
$check_delete_rlst = $check_delete_info->row();
$check_delete_info->next_result();
$unique_info = explode(",",$check_delete_rlst->unique_field ?? "");
if(in_array('1', $unique_info)){
return TRUE;
}else{
return FALSE;
}
}
/* ==============================================================*/
/* ============ MONHTLY IMPORT OPERATION - START ================*/
/* ==============================================================*/
//IMPORT FILE VIEW INFORMATION
public function 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);
}
$data['module_id'] = "monthly_input_fms";
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$category_list[""] = "---- Select Category ----";
foreach ($role_result as $for) {
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "monthly_input_fms" and trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_format_drop[""] = "---- Excel Format ----";
foreach ($excel_result as $excel) {
$prime_excel_format_id = $excel->prime_excel_format_id;
$excel_name = $excel->excel_name;
$excel_format_drop[$prime_excel_format_id] = $excel_name;
}
$data['excel_format_drop'] = $excel_format_drop;
//for time sheet
$data['personal_code'] = $this->input->post('personal_code');
$data['project'] = $this->input->post('project');
$wbs_element_str = implode(",",$this->input->post('fil_wbs_element') ?? []);
$fil_wbs_element = str_replace(',','","',$wbs_element_str);
$data['fil_wbs_element'] = $fil_wbs_element;
$data['fil_emp_code'] = $this->input->post('fil_emp_code');
$data['encKey'] = $this->generateKey();
$this->load->view('time_sheet/import', $data);
}
//NEHA EDIT START 06APR2020
public function cats_excel($Payload){
$_POST = $this->cryptoDecrypt(base64_decode(urldecode($Payload)));
$module_id = $this->input->post('module_id');
$excel_format = $this->input->post('excel_format');
$excel_format_qry = 'select excel_name,view_name,excel_line_column_name,excel_line_value from cw_util_excel_format_line inner join cw_form_setting on cw_form_setting.label_name = excel_line_column_name inner join cw_util_excel_format on cw_util_excel_format.prime_excel_format_id = cw_util_excel_format_line.prime_excel_format_id where excel_line_module_id = "'.$module_id.'" and cw_util_excel_format_line.prime_excel_format_id ="'.$excel_format.'" and cw_util_excel_format_line.trans_status = 1 and cw_util_excel_format.trans_status = 1 GROUP BY cw_form_setting.label_name';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_name = str_replace(' ', '_', $excel_result[0]->excel_name);
// Rename worksheet name
$filename = $excel_name.".xls"; //save our workbook as this file name
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
# Set the first row as the header row
foreach($excel_result as $excel){
$excel_line_column_name = $excel->view_name;
$excel_line_value = $excel->excel_line_value;
$worksheet->getCell($excel_line_value.'1')->setValue($excel_line_column_name);
}
ob_end_clean();
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename= "'.$filename.'"');
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');
ob_end_clean();
echo json_encode(array('success' => TRUE, 'output' => $worksheet));
exit(0);
}
//SAVE MONTHLY IMPORT FILE PATH
public function save_import(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$module_id = $this->input->post('module_id');
$excel_format = $this->input->post('excel_format');
$excel_file_path = $this->input->post('excel_file_path');
$excel_sheet_name = $this->input->post('excel_sheet_name');
$excel_start_row = $this->input->post('excel_start_row');
$excel_end_row = $this->input->post('excel_end_row');
$process_month = $this->input->post('process_month');
$logged_id = $this->session->userdata('logged_id');
$result_input = $this->input->post('resultsInput');
$today_date = date("Y-m-d H:i:s");
$import_query = 'insert into cw_month_import (module_id,excel_format,excel_file_path,excel_sheet_name,excel_start_row,excel_end_row,category,process_month,trans_created_by,trans_created_date) value ("' . $module_id . '","' . $excel_format . '","' . $excel_file_path . '","' . $excel_sheet_name . '","' . $excel_start_row . '","' . $excel_end_row . '","' . $category . '","' . $process_month . '","' . $logged_id . '","' . $today_date . '")';
$import_info = $this->db->query("CALL sp_a_run ('INSERT','$import_query')");
$import_result = $import_info->result();
$import_info->next_result();
$import_id = $import_result[0]->ins_id;
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$fil_wbs = $this->input->post('fil_wbs_element');
if($fil_wbs === null || $fil_wbs == ""){
$fil_wbs = [];
}
$wbs_element_str = implode(",", $fil_wbs ?? []);
$fil_wbs_element = str_replace(',','","',$wbs_element_str);
echo $this->do_monthly_excel_import($import_id,$personal_code,$project,$fil_wbs_element);
}
//IMPORT DATA FROM FILE PATH
public function do_monthly_excel_import($import_id,$personal_code,$project,$fil_wbs_element){
$created_on = date("Y-m-d H:i:s");
if($import_id < 0){
return json_encode(array(
'success' => false,
'message' => "Invalid file upload"
));
}
$excel_path_qry = 'select * from cw_month_import where import_id = "'.$import_id.'"';
$excel_path_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_path_qry')");
$excel_path_result = $excel_path_info->result();
$excel_path_info->next_result();
if(!$excel_path_result){
return json_encode(array(
'success' => false,
'message' => "Invalid file upload"
));
}else{
$excel_file_path = $excel_path_result[0]->excel_file_path;
$module_id = $excel_path_result[0]->module_id;
$excel_format = $excel_path_result[0]->excel_format;
$excel_sheet_name = (int)$excel_path_result[0]->excel_sheet_name;
$excel_row_start = (int)$excel_path_result[0]->excel_start_row;
$excel_row_end = (int)$excel_path_result[0]->excel_end_row;
$process_month = $excel_path_result[0]->process_month;
$process_mon_date = date("Y-m-d",strtotime("01-".$process_month));
$start_date = date("Y-m-t",strtotime("01-".$process_month));
$end_date = date("Y-m-d",strtotime("01-".$process_month));
//Function verifies if the employee codes in Excel match those in the employee master list! ->NB
$emp_qry = 'select employee_code,date_of_joining,date_of_birth,role,emp_name,prime_employees_id,termination_status,emp_cost_center_code from cw_employees where cw_employees.trans_status = 1 and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m-%d") <= "'.$start_date.'" and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, "%Y-%m-%d") >= "'.$end_date.'")) and cw_employees.entry_flag = "e" ';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_data_result = $emp_data->result();
$emp_data->next_result();
$emp_code_result = array();
foreach($emp_data_result as $v){
$return_array = array();
$return_array['employee_data'] = $v;
$return_array['employee_code'] = $v->employee_code;
$emp_code_result[] = $return_array;
}
$emp_code_data = array_column($emp_code_result ?? [], 'employee_data', 'employee_code');
//Function verifies if the employee_code,wbs in Excel with the Monthly Input FMS!
$monthly_fms_qry = 'SELECT employee_code,wbs_element,project_id,position FROM cw_monthly_input_fms WHERE trans_status = 1 AND entry_status IN(0,2) AND check_status IN (0,2) AND process_month = "'.$process_month.'" AND termination_status = 0 AND personal_code = "'.$personal_code.'" AND project_id = "'.$project.'" ';
$monthly_fms_data = $this->db->query("CALL sp_a_run ('SELECT','$monthly_fms_qry')");
$monthly_fms_result = $monthly_fms_data->result();
$monthly_fms_data->next_result();
$mon_fms_result = array();
$mon_wbs_result = array();
foreach($monthly_fms_result as $v){
$return_array = array();
$return_array['employee_data'] = $v;
$return_array['employee_code'] = $v->employee_code;
$return_array['wbs_data'] = $v;
$return_array['wbs_element'] = $v->wbs_element;
$return_array['project_data'] = $v;
$return_array['project_id'] = $v->project_id;
$mon_fms_result[] = $return_array;
$mon_wbs_result[$v->employee_code][$v->wbs_element][$v->position] = $v->employee_code;
}
$mon_fms_data = array_column($mon_fms_result ?? [], 'employee_data', 'employee_code');
$mon_wbs_data = array_column($mon_fms_result ?? [], 'wbs_data', 'wbs_element');
$mon_pro_data = array_column($mon_fms_result ?? [], 'project_data', 'project_id');
//Function checks the activity_no in Excel whether process_month <=,>= activity table start and end date!
$activity_qry = 'SELECT act_no FROM cw_sap_activity WHERE cw_sap_activity.trans_status = 1 AND date_format(cw_sap_activity.act_start_date, "%Y-%m-%d") <= "'.$start_date.'" and date_format(cw_sap_activity.act_end_date, "%Y-%m-%d") >= "'.$end_date.'" GROUP BY act_no';
$activity_data = $this->db->query("CALL sp_a_run ('SELECT','$activity_qry')");
$activity_result = $activity_data->result();
$activity_data->next_result();
$act_rslt = array();
foreach($activity_result as $v){
$return_array = array();
$return_array['activity_data'] = $v;
$return_array['act_no'] = $v->act_no;
$act_rslt[] = $return_array;
}
$activity_data = array_column($act_rslt ?? [], 'activity_data', 'act_no');
$lock_month_qry = 'select * from cw_monthly_input_lock where lock_month = "' . $process_month . '" and status = 1 and trans_status = 1';
$lock_month_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_month_qry')");
$lock_month_result = $lock_month_data->result();
$lock_month_data->next_result();
$lock_num_rows = $lock_month_data->num_rows();
if((int) $lock_num_rows > 0){
return json_encode(array(
'success' => false,
'message' => "Monthly input is locked this month, please unlocked to upload the files"
));
exit(0);
}
$format_qry = 'select * from cw_util_excel_format where prime_excel_format_id = "' . $excel_format . '" and cw_util_excel_format.trans_status = 1';
$format_info = $this->db->query("CALL sp_a_run ('SELECT','$format_qry')");
$format_rslt = $format_info->result();
$format_info->next_result();
if (!$format_rslt) {
return json_encode(array(
'success' => false,
'message' => "Please add excel format before import"
));
}else{
$this->search_info(); //NB[17-10-23]
$exclude_values = array('role', 'process_month', 'date_of_joining', 'date_of_birth', 'w_off', 'stop_pay_code', 'hold_reason', 'termination_status','sub_date','msal_apr_date','msal_rej_date','chk_date');
$label_values = explode(',', $this->select_query ?? "");
$filtered_values = array_diff($label_values ?? [], $exclude_values ?? []);
$quoted_values = array_map(function($value){
return '"' . trim($value) . '"';
}, $filtered_values);
$in_clause = implode(',', $quoted_values) ?? [];
$excel_qry = 'SELECT * FROM cw_form_setting WHERE label_name IN ('.$in_clause.') AND FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) AND (earn_month_check = "1" OR deduction_month_check = "1") AND prime_module_id = "employees" GROUP BY label_name ORDER BY monthly_input_sort ';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_qry')");
$excel_value = $excel_format->result_array();
$excel_format->next_result();
//Include Alphabets in the array.
$letters = range('A', 'Z');
$excel_format_result = array();
foreach($excel_value as $key => $row){
$first_letter = $letters[floor($key / count($letters ?? [])) - 1];
$second_letter = $letters[$key % count($letters ?? [])];
if($first_letter === null){
$first_letter = '';
}
$row['excel_line_value'] = $first_letter . $second_letter;
$excel_format_result[] = $row;
}
if(!$excel_format_result){
return json_encode(array(
'success' => false,
'message' => "Please map excel cell column before import"
));
}else{
try {
$excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($excel_file_path);
}
catch (Exception $e) {
die('Error loading file "' . pathinfo($excel_file_path, PATHINFO_BASENAME) . '": ' . $e->getMessage());
return json_encode(array(
'success' => false,
'message' => "Invalid file or path"
));
}
//---------------------IMPORTANT CODE FOR MI IMPORT PROCESS---------------
//Sap activity number get code based on wbs.
$sap_act_no_qry = 'select prime_sap_activity_id as prime_id,act_no,act_wbs_id from cw_sap_activity where cw_sap_activity.trans_status = 1 GROUP BY cw_sap_activity.prime_sap_activity_id,cw_sap_activity.act_wbs_id';
$sap_act_no_info = $this->db->query("CALL sp_a_run ('SELECT','$sap_act_no_qry')");
$sap_act_no_rslt = $sap_act_no_info->result_array();
$sap_act_no_info->next_result();
$sap_act_no_arr = array();
foreach ($sap_act_no_rslt as $arr) {
$sap_act_no_arr[$arr['act_wbs_id']][$arr['act_no']] = $arr['prime_id'];
}
//get pay structure details for dot and ot input check array.
$pay_struct_qry = 'select cw_pay_structure.wbs_element,cw_pay_structure.wbs_element,cw_pay_structure.position,cw_pay_structure.activity_no,hrms_field_name,amount from cw_pay_structure inner join cw_pay_structure_line on cw_pay_structure.prime_pay_structure_id = cw_pay_structure_line.prime_pay_structure_id where cw_pay_structure.personal_code = "'.$personal_code.'" and cw_pay_structure.from_date <= "'.$process_mon_date.'" and cw_pay_structure.to_date >= "'.$process_mon_date.'" and cw_pay_structure.trans_status = 1 and cw_pay_structure_line.trans_status = 1';
$pay_struct_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_struct_qry')");
$pay_struct_rslt = $pay_struct_info->result_array();
$pay_struct_info->next_result();
$pay_struct_arr = array();
array_walk($pay_struct_rslt, function($v, $k) use(&$pay_struct_arr){
$pay_wbs = $v["wbs_element"];
$pay_pos = $v["position"];
$pay_act_no = $v["activity_no"];
$field_name = $v["hrms_field_name"];
$amount = $v["amount"];
$pay_struct_arr[$pay_wbs][$pay_pos][$pay_act_no][$field_name] = $v;
});
//DR CODE FOR DYNAMICALLY TO CHECK A MONTHLY INPUT PROCESS (BASED ON MI SETTINGS MODULE IMPORT CHECK BASED INPUT)
$mi_imp_check_col_rslt = $this->mi_import_check_col_setting();
//json object to array convertion
$mi_imp_check_col_rslt = json_decode(json_encode($mi_imp_check_col_rslt), true);
$mi_imp_check_col_arr = array();
foreach ($mi_imp_check_col_rslt as $arr){
$mi_imp_check_col_arr[$arr['label_name']] = $arr['label_name'];
}
//array for check a exist mi from mi table based on this array columns
$mi_imp_exist_col_arr = array_map(function($column){
return $column['label_name'];
}, $mi_imp_check_col_rslt ?? []);
//QRY FOR GET A FORM SETTING BASED EMPLOYEE MASTER PAYROLL COLUMNS(INPUTS)
$form_qry = 'select prime_form_id,prime_module_id,label_name,view_name,field_type,pick_list_type,pick_list,pick_table,pick_display_value,pick_list_import,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "employees" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and (earn_month_check = "1" OR deduction_month_check = "1")';
$form_info = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_info_rslt = $form_info->result();
$form_info->next_result();
//ARRAY FOR CHECK A PICKLIST DATA ARE VALID OR NOT
$all_pick_import_arr = $this->pay_picklist_get_function("import",$form_info_rslt,"");
//--------------------- IMPORTANT CODE FOR MI IMPORT PROCESS END ---------------
$sheet = $excel_obj->getSheet($excel_sheet_name);
if($excel_row_end){
$total_rows = $excel_row_end;
}else{
$total_rows = $sheet->getHighestRow();
}
$headerRow = $sheet->getRowIterator(1)->current();
$highest_column = 0;
foreach($headerRow->getCellIterator() as $cell){
$column = $cell->getColumn();
$columnIndex = $this->excelColumnLetterToNumber($column);
if($columnIndex > $highest_column && !empty($cell->getValue())){
$highest_column = $columnIndex;
}
}
$secondLastColumnIndex = $highest_column - 2;
$lastColumnIndex = $highest_column - 1;
$error_info = array();
for($row = $excel_row_start; $row <= $total_rows; $row++){
$secondLastColumnLetter = $this->excelColumnNumberToLetter($secondLastColumnIndex + 1);
$lastColumnLetter = $this->excelColumnNumberToLetter($lastColumnIndex + 1);
// Clear the values in the second-last and last columns for the current row
$sheet->setCellValue($secondLastColumnLetter . $row, "");
$sheet->setCellValue($lastColumnLetter . $row, "");
$exist_val = "";
$pay_struct_exist_val = "";
$prime_column_val = "";
$prime_cell_val = "";
$status_info = array();
$status_info["Excel Row"] = $row;
$prime_upd_query = "";
foreach($excel_format_result as $excel_info){
$column_name = $excel_info['label_name'];
$column_value = $excel_info['excel_line_value'];
$field_type = (int)$excel_info['field_type'];
$view_name = $excel_info['view_name'];
$get_cell_value = trim($sheet->getCell("$column_value$row")->getCalculatedValue());
if($column_name === "employee_code"){
$employee_code = $get_cell_value;
$emp_data = $emp_code_data[$get_cell_value];
$emp_doj = $emp_data->date_of_joining;
$category = $emp_data->role;
$emp_status = (int)$emp_data->termination_status;
$cost_cen_code = $emp_data->emp_cost_center_code;
if(!array_key_exists($get_cell_value, $emp_code_data ?? [])){ //NB
$error_info["$column_value$row"] = "Invalid - Employee code[$employee_code] doesn't exist or Inactive or Please check date of joining..!";
}
if(!array_key_exists($get_cell_value, $mon_fms_data ?? [])){
$error_info["$column_value$row"] = "Invalid - Employee code[$employee_code] not exist in this CATS..!";
}
//EXIST MI CHECK SO EMPLOYEE CODE COLUMN ADD FOR MANDATORY EXIST CHECK PROCESS
$exist_val = $column_name . ' = "' . $get_cell_value . '" and process_month = "' . $process_month . '" and ';
}else
if($field_type === 5 || $field_type === 9){
if(!$get_cell_value){
$show_value = ucwords(str_replace("_"," ",$column_name));
$error_info["$column_value$row"] = "$show_value Should Not Empty..!";
}else{
if($column_name === "project_id"){
if(!array_key_exists($get_cell_value, $mon_pro_data ?? [])){
$error_info["$column_value$row"] = "Invalid - Project[$get_cell_value] not exist in this CATS..!";
}
}
if($column_name === "wbs_element"){
$wbs_element = $get_cell_value;
if(!array_key_exists($get_cell_value, $mon_wbs_data ?? [])){
$error_info["$column_value$row"] = "Invalid - WBS[$get_cell_value] not exist in this CATS..!";
}else
if(!$mon_wbs_result[$employee_code][$wbs_element]){
$error_info["$column_value$row"] = "Invalid - WBS[$get_cell_value] not exist for this Employee in this CATS..!";
}
}
if($column_name === "activity_no"){
if(!array_key_exists($get_cell_value, $activity_data ?? [])){
$error_info["$column_value$row"] = "Invalid - Activity No[$get_cell_value] is Expired..!";
}else
if($wbs_element){
$get_key_val = $sap_act_no_arr[$wbs_element][$get_cell_value];
if(!$get_key_val){
$error_info["$column_value$row"] = "Invalid - Unknown Activity No[$get_cell_value] Mapped..!";
}
}else{
$error_info["$column_value$row"] = "Activity Number Column should Map after WBS Element Column.!";
}
$activity_no = $get_key_val;
}else{
$get_key_val = $all_pick_import_arr[$column_name][$get_cell_value];
if(!$get_key_val){
$error_info["$column_value$row"] = "Unknown ($get_cell_value) Data Mapped..!";
}
if($column_name === "position"){
$position = $get_key_val;
if(!$mon_wbs_result[$employee_code][$wbs_element][$position]){
$error_info["$column_value$row"] = "Invalid - Position[$position] not exist for this Employee in this CATS..!";
}
}
}
$get_cell_value = $get_key_val;
}
}else{
//DECIMAL FIELDS ONLY
if($field_type === 2){
//Month Days Validation
if($column_name === "md"){
$month_days = (float)$get_cell_value;
if($month_days > 31){
$error_info["$column_value$row"] = "Month Days Should Lesser than 31 Days..!";
}else
if($month_days < 0){
$error_info["$column_value$row"] = "Month Days Should Higher than Zero..!";
}
}
//Payroll and billable employee validation start
if($cost_cen_code === "" || $cost_cen_code === "0" || $cost_cen_code === 0){
if($column_name === "buffer_pd"){
$buffer_pd = number_format((float)$get_cell_value, 2, '.', '');
// $buffer_pd = (float)$get_cell_value;
if($buffer_pd !== "0.00" && $buffer_pd !== 0.00){
$error_info["$column_value$row"] = "Buffer Days not allow to add for normal Employees.!";
}
}
if($column_name === "pd"){
$paid_days = (float)$get_cell_value;
if(!$month_days){
$error_info["$column_value$row"] = "Paid Days Column Should Map After Month Days Column or Month days should not be Zero..!";
}else
if($paid_days > 31){
$error_info["$column_value$row"] = "Paid Days Should Add Below 31 Days..!";
}else
if($month_days < $paid_days){
$error_info["$column_value$row"] = "Paid Days Should Lesser than Month Days..!";
}
}
if($column_name === "reliever_pd"){
$rel_pd_days = (float)$get_cell_value;
if(!$month_days){
$error_info["$column_value$row"] = "Reliever Days Column Should Map After Month Days Column";
}else
if($rel_pd_days > 31){
$error_info["$column_value$row"] = "Reliever Days Should Add Below 31 Days..!";
}else
if($month_days < $rel_pd_days){
$error_info["$column_value$row"] = "Reliever Days Should Lesser than Month Days..!";
}
}
if($column_name === "nb_pd"){
$nb_pd_days = (float)$get_cell_value;
if(!$month_days){
$error_info["$column_value$row"] = "Non Billable Days Column Should Map After Month Days Column";
}else
if($nb_pd_days > 31){
$error_info["$column_value$row"] = "Non Billable Days Should Add Below 31 Days..!";
}else
if($month_days < $nb_pd_days){
$error_info["$column_value$row"] = "Non Billable Days Should Lesser than Month Days..!";
}
}
}else//FOR BILLABLE
if($cost_cen_code){
if($column_name !== "buffer_pd" && $column_name !== "md"){
$other_input = number_format((float)$get_cell_value, 2, '.', '');
// $other_input = (float)$get_cell_value;
if($other_input !== "0.00" && $other_input !== 0.00){
$error_info["$column_value$row"] = "For Billable Employees can input only Month Days & Buffer Days..!";
}
}
}
//Payroll and billable employee validation end
//PAY STRUCTURE BASED INPUTS VALIDATE
if($column_name === "ot_hrs"){
$ot_hrs_val = (float)number_format((float)$get_cell_value, 2, '.', '');
}
if($column_name === "ot_days"){
$ot_days_val = (float)number_format((float)$get_cell_value, 2, '.', '');
}
}
}
//Exist Check Condition from mi entry table
if (in_array($column_name, $mi_imp_exist_col_arr)) {
$exist_val .= $column_name . ' = "' . $get_cell_value . '" and ';
if($column_name !== 'project_id' && $column_name !== 'activity_no'){
//As discussed with porul on 01sep
$pay_struct_exist_val .= $column_name . ' = "' . $get_cell_value . '" and ';
}
}
}
//SUM OF PAID DAYS CALCULATION FOR PAYROLL EMPLOYEES
$sum_days = $paid_days + $rel_pd_days + $nb_pd_days;
if($cost_cen_code === "" || $cost_cen_code === "0" || $cost_cen_code === 0){
if($month_days < $sum_days){
$error_info["$column_value$row"] = "Sum of Paid Days Greater than Month Days..!";
}
}
$exist_val = rtrim($exist_val, " and ");
$pay_struct_exist_val = rtrim($pay_struct_exist_val, " and ");
//pay structure exist check
$pay_struct_ext_qry = 'select count(cw_pay_structure.prime_pay_structure_id) exist_count from cw_pay_structure where cw_pay_structure.trans_status = 1 and '.$pay_struct_exist_val.' and cw_pay_structure.from_date <= "'.$process_mon_date.'" and cw_pay_structure.to_date >= "'. $process_mon_date.'"';
// and cw_pay_structure.status = 1
$pay_struct_ext_info = $this->db->query("CALL sp_a_run ('RUN','$pay_struct_ext_qry')");
$pay_struct_ext_rslt = $pay_struct_ext_info->result();
$pay_struct_ext_info->next_result();
$pay_struct_ext_count = $pay_struct_ext_rslt[0]->exist_count;
if(!(int)$pay_struct_ext_count){
$error_info["$column_value$row"] = "Pay Structure not Mapped for this Combination or Paystructure Apply Date not Applicable for this Process Month.!";
}else{
//Monthly Input Fms exist check for this Month
$exist_query = 'select count(prime_monthly_input_fms_id) exist_count,prime_monthly_input_fms_id,trans_status,entry_status,check_status from cw_monthly_input_fms where cw_monthly_input_fms.trans_status = 1 and '.$exist_val.' and cw_monthly_input_fms.process_month = "'. $process_month.'"';
$exist_info = $this->db->query("CALL sp_a_run ('RUN','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
$exist_count = (int)$exist_result[0]->exist_count;
//IF ALREADY EXIST
if($exist_count > 0){
$entry_status = (int)$exist_result[0]->entry_status;
$check_status = (int)$exist_result[0]->check_status;
if($entry_status === 1){
// || $check_status === 1
$error_info["$column_value$row"] = "Could not Update..! Because Maker Status Already Approved..!";
}
}else{//IF NOT ALREADY EXIST THEN CHECK BY EMPLOYEE MASTER TABLE
//CURRENT EMPLOYEE MASTER DATA CHECK IF EXIST WHEN (CORRECT) IF NOT EXIST (WRONG)
//Comment by sathish 29sep2023
/*$valid_emp_qry = 'select prime_employees_id from cw_employees where cw_employees.trans_status = 1 and '.$pay_struct_exist_val.' and cw_employees.employee_code = "'. $employee_code.'" and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, "%Y-%m") >= "'.$qry_search_month.'"))';
$valid_emp_info = $this->db->query("CALL sp_a_run ('RUN','$valid_emp_qry')");
$valid_emp_rslt = $valid_emp_info->result();
$valid_emp_info->next_result();
$valid_emp_id = (int)$valid_emp_rslt[0]->prime_employees_id;
if(!$valid_emp_id){
$error_info["$column_value$row"] = "Pay Structure Combination are not Mapped Correctly for this Employee.!Please Check Employee Master..!";
}*/
}
//OT HOUR RATE CHECK BASED PAY STRUCTURE ARRAY
if(array_key_exists("ot_hrate",$pay_struct_arr[$wbs_element][$position][$activity_no] ?? [])){
$ot_hrate = $pay_struct_arr[$wbs_element][$position][$activity_no]['ot_hrate']['amount'];
$ot_hrate = (float)number_format((float)$ot_hrate, 2, '.', '');
if($ot_hrate === 0.00 && $ot_hrs_val > 0){
$error_info["$column_value$row"] = "OT Hourly Rate not Available in Pay Structure..!";
}
}
//OT DAY RATE CHECK BASED PAY STRUCTURE ARRAY
if(array_key_exists("ot_drate",$pay_struct_arr[$wbs_element][$position][$activity_no] ?? [])){
$ot_drate = $pay_struct_arr[$wbs_element][$position][$activity_no]['ot_drate']['amount'];
$ot_drate = (float)number_format((float)$ot_drate, 2, '.', '');
if($ot_drate === 0.00 && $ot_days_val > 0){
$error_info["$column_value$row"] = "OT Days Rate not Available in Pay Structure..!";
}
}
}
}
$can_process_count = count($error_info ?? []);
if ((int) $can_process_count > 0) {
return json_encode(array(
'success' => true,
'message' => "File Has Error",
'error_info' => $error_info
));
}else{
$status_array = array();
if($total_rows){
for($row = $excel_row_start; $row <= $total_rows; $row++){
$secondLastColumnLetter = $this->excelColumnNumberToLetter($secondLastColumnIndex + 1);
$lastColumnLetter = $this->excelColumnNumberToLetter($lastColumnIndex + 1);
// Clear the values in the second-last and last columns for the current row
$sheet->setCellValue($secondLastColumnLetter . $row, "");
$sheet->setCellValue($lastColumnLetter . $row, "");
$exist_val = "";
$pay_struct_exist_val = "";
$prime_column_val = "";
$prime_cell_val = "";
$status_info = array();
$status_info["Excel Row"] = $row;
$prime_upd_query = "";
$inp_entry_status = 0;
foreach ($excel_format_result as $excel_info) {
$column_name = $excel_info['label_name'];
$column_value = $excel_info['excel_line_value'];
$field_type = (int)$excel_info['field_type'];
$get_cell_value = trim($sheet->getCell("$column_value$row")->getCalculatedValue());
if($column_name === "employee_code"){
$employee_code = $get_cell_value;
$emp_data = $emp_code_data[$get_cell_value];
$doj = $emp_data->date_of_joining;
$dob = $emp_data->date_of_birth;
$emp_id = $emp_data->prime_employees_id;
$cat = $emp_data->role;
$emp_name = $emp_data->emp_name;
$emp_status = $emp_data->termination_status;
$emp_cell_val = $emp_id;
//FOR BILLABLE AND PAYROLL EMPLOYEE BASED ENTRY STATUS UPDATE
$cost_cen_code = $emp_data->emp_cost_center_code;
//EXIST MI CHECK SO EMPLOYEE CODE COLUMN ADD FOR MANDATORY EXIST CHECK PROCESS
$exist_val = $column_name . ' = "' . $get_cell_value . '" and process_month = "' . $process_month . '" and ';
}else
if($field_type === 5 || $field_type === 9){
if($column_name === "wbs_element"){
$wbs_element = $get_cell_value;
}
if($column_name === "activity_no"){
if($wbs_element){
$get_cell_value = $sap_act_no_arr[$wbs_element][$get_cell_value];
}
}else{
$get_cell_value = $all_pick_import_arr[$column_name][$get_cell_value];
}
}
//CATE ENTRY STATUS VALUE GET FOR ENTRY STATUS UPDATE START
if($cost_cen_code === "" || $cost_cen_code === "0" || $cost_cen_code === 0){
if($column_name === "pd"){
$get_cell_value = number_format((float)$get_cell_value, 2, '.', '');
if($get_cell_value === "0.00" || $get_cell_value === 0.00){
$inp_entry_status = 0;
}else{
$inp_entry_status = 2;
}
}
}else
if($cost_cen_code){
if($column_name === "buffer_pd"){
$get_cell_value = number_format((float)$get_cell_value, 2, '.', '');
if($get_cell_value === "0.00" || $get_cell_value === 0.00){
$inp_entry_status = 0;
}else{
$inp_entry_status = 2;
}
}
}
//CATE ENTRY STATUS VALUE GET FOR ENTRY STATUS UPDATE END
if($column_name !== "emp_name" && $column_name !== "personal_code" && $column_name !== "project_id" && $column_name !== "wbs_element" && $column_name !== "position" && $column_name !== "activity_no" && $column_name !== "employee_code"){
$prime_column_val .= $column_name . ",";
$prime_cell_val .= '"' . $get_cell_value . '",';
$update_column_val = $column_name;
$update_cell_val = '"' . $get_cell_value . '",';
$prime_upd_query .= $update_column_val . "=" . $update_cell_val;
}
//Exist Check Condition from mi entry table
if (in_array($column_name, $mi_imp_exist_col_arr)) {
$exist_val .= $column_name . ' = "' . $get_cell_value . '" and ';
}
}
$exist_val = rtrim($exist_val, " and ");
//monthly input exist check
$exist_query = 'select count(prime_monthly_input_fms_id) exist_count,prime_monthly_input_fms_id,trans_status,entry_status,check_status from cw_monthly_input_fms where cw_monthly_input_fms.trans_status = 1 and '.$exist_val.' and cw_monthly_input_fms.process_month = "'.$process_month.'"';
$exist_info = $this->db->query("CALL sp_a_run ('RUN','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
$exist_count = $exist_result[0]->exist_count;
//IF EXIST COUNT 0 COULD INSERT A DATA
// if((int)$exist_count === 0){
// $prime_column_val .= "employees_id,emp_name,role,date_of_joining,date_of_birth,process_month,trans_created_by,trans_created_date,entry_status,check_status,payroll_status,termination_status";
// $prime_cell_val .= '"'.$emp_cell_val.'","'.$emp_name.'","'.$cat.'","'.$doj.'","'.$dob.'","'.$process_month.'","'.$this->logged_id.'","'.$created_on.'","'.$inp_entry_status.'","0","2","'.$emp_status.'"';
// $prime_column_val = rtrim($prime_column_val, ",");
// $prime_cell_val = rtrim($prime_cell_val, ",");
// $prime_query = "insert into cw_monthly_input_fms ($prime_column_val) VALUES ($prime_cell_val)";
// $insert_info = $this->db->query("CALL sp_a_run ('RUN','$prime_query')");
// $status_info['Status'] = "Inserted to DB";
// }else //IF EXIST COUNT NOT 0 THEN WE CHECK SOME STATUS AND THEN UPDATE
if((int)$exist_count > 0){
$upd_prime_id = (int)$exist_result[0]->prime_monthly_input_fms_id;
$entry_status = (int)$exist_result[0]->entry_status;
$check_status = (int)$exist_result[0]->check_status;
$trans_status = (int)$exist_result[0]->trans_status;
if($entry_status !== 1){
// && $check_status === 2
$prime_upd_query .= 'trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'",trans_deleted_by = NULL,trans_deleted_date = NULL,entry_status = "'.$inp_entry_status.'",payroll_status = "2",termination_status = "'.$emp_status.'"';
// ,check_status = "2"
if($upd_prime_id){
$upd_fms_query = 'UPDATE cw_monthly_input_fms SET '.$prime_upd_query.' WHERE prime_monthly_input_fms_id = "'.$upd_prime_id.'"';
$upd_fms_info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_fms_query')");
$status_info['Status'] = "Updated to DB";
}
}
}
$status_array[] = $status_info;
}
}else{
$imp_sts = true;
}
}
}
if($imp_sts){
$table_info = "";
$status = False;
$msg = "Invalid Excel Format to Import";
}else{
$status = true;
$msg = "Successfully files imported in database!!!";
$table_info = $this->get_excel_import_ui($status_array);
}
return json_encode(array(
'success' => $status,
'message' => $msg,
'table_info' => $table_info
));
}
}
}
//DR CODE START FOR PROJECT ID FETCH BASED ON PERSONAL CODE
public function project_id_fetch(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$personal_code = $this->input->post('personal_code');
$search_month = $this->input->post('search_month');
//FOR ACTIVE PROJECT ONLY SHOULD SHOW IN PICKLIST
$start_date = date("Y-m-t",strtotime('01-'.$search_month));
$end_date = date("Y-m-d",strtotime('01-'.$search_month));
$pro_id_qry = 'SELECT pro_id,pro_desc FROM `cw_sap_project` where cw_sap_project.pro_personal_area_id = "'.$personal_code.'" and cw_sap_project.pro_start_date <= "'.$start_date.'" and cw_sap_project.pro_end_date >= "'.$end_date.'" and cw_sap_project.trans_status = 1';
$pro_id_info = $this->db->query("CALL sp_a_run ('SELECT','$pro_id_qry')");
$pro_id_rslt = $pro_id_info->result_array();
$pro_id_info->next_result();
echo json_encode($pro_id_rslt);
}
//DR CODE END FOR PROJECT ID FETCH BASED ON PERSONAL CODE
//DR CODE FOR GET PAY STRUCTURE DETAILS 22NOV22 START - Changed by BSK 23MAY2024
public function pay_struct_details(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$process_month = $this->input->post('process_month');
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
# and cw_pay_structure.activity_no = cw_monthly_input_fms.activity_no
//$pay_struct_qry = 'select cw_monthly_input_fms.prime_monthly_input_fms_id mi_id,hrms_field_name,amount from cw_monthly_input_fms inner join cw_pay_structure on (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 ) inner join cw_pay_structure_line on cw_pay_structure.prime_pay_structure_id = cw_pay_structure_line.prime_pay_structure_id where cw_monthly_input_fms.personal_code = "'.$personal_code.'" and cw_monthly_input_fms.project_id = "'.$project.'" and cw_monthly_input_fms.process_month = "'.$process_month.'" and date_format(cw_pay_structure.from_date, "%Y-%m") <= date_format(str_to_date("01-'.$process_month.'", "%d-%m-%Y") , "%Y-%m") and date_format(cw_pay_structure.to_date, "%Y-%m") >= date_format(str_to_date("01-'.$process_month.'", "%d-%m-%Y") , "%Y-%m") and cw_monthly_input_fms.trans_status = 1 and cw_pay_structure.trans_status = 1 and cw_pay_structure_line.trans_status = 1 and cw_monthly_input_fms.entry_status = 2 ORDER BY prime_monthly_input_fms_id ASC';
$mi_qry = 'SELECT cw_monthly_input_fms.prime_monthly_input_fms_id mi_id,cw_monthly_input_fms.payroll,cw_monthly_input_fms.md,cw_monthly_input_fms.pd,cw_monthly_input_fms.w_off,cw_monthly_input_fms.nb_pd,cw_monthly_input_fms.reliever_pd,cw_monthly_input_fms.buffer_pd,cw_monthly_input_fms.ot_hrs,cw_monthly_input_fms.ot_days,cw_monthly_input_fms.stop_pay_code,cw_monthly_input_fms.hold_reason,cw_pay_structure.prime_pay_structure_id FROM cw_monthly_input_fms INNER JOIN cw_pay_structure ON (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 cw_monthly_input_fms.personal_code = "'.$personal_code.'" AND cw_monthly_input_fms.project_id = "'.$project.'" AND DATE_FORMAT(cw_pay_structure.from_date, "%Y-%m") <= date_format(str_to_date("01-'.$process_month.'", "%d-%m-%Y") , "%Y-%m") AND DATE_FORMAT(cw_pay_structure.to_date, "%Y-%m") >= date_format(str_to_date("01-'.$process_month.'", "%d-%m-%Y") , "%Y-%m") AND cw_monthly_input_fms.process_month = "'.$process_month.'" AND cw_monthly_input_fms.trans_status = 1 AND cw_pay_structure.trans_status = 1 AND cw_monthly_input_fms.entry_status = 2 GROUP BY prime_monthly_input_fms_id';
$mi_qry_info = $this->db->query("CALL sp_a_run ('SELECT','$mi_qry')");
$mi_qry_rslt = $mi_qry_info->result_array();
$mi_qry_info->next_result();
//Get Paystructure data to validate "ot_hrate","ot_drate"
$pay_struct_qry = 'SELECT cw_pay_structure.prime_pay_structure_id,hrms_field_name,amount FROM cw_pay_structure INNER JOIN cw_sap_wbs ON cw_sap_wbs.wbs_id = cw_pay_structure.wbs_element INNER JOIN cw_pay_structure_line ON cw_pay_structure.prime_pay_structure_id = cw_pay_structure_line.prime_pay_structure_id WHERE cw_pay_structure.personal_code = "'.$personal_code.'" AND cw_sap_wbs.wbs_project_id = "'.$project.'" AND DATE_FORMAT(cw_pay_structure.from_date, "%Y-%m") <= date_format(str_to_date("01-'.$process_month.'", "%d-%m-%Y") , "%Y-%m") AND DATE_FORMAT(cw_pay_structure.to_date, "%Y-%m") >= date_format(str_to_date("01-'.$process_month.'", "%d-%m-%Y") , "%Y-%m") AND cw_sap_wbs.trans_status = 1 AND cw_pay_structure.trans_status = 1 AND cw_pay_structure_line.trans_status = 1 AND cw_pay_structure_line.hrms_field_name IN ("ot_hrate","ot_drate")';
$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();
//Split result array to map by pay struct id
$split_arr = array();
foreach ($pay_struct_rslt as $arr){
$split_arr[$arr['prime_pay_structure_id']][$arr['hrms_field_name']] = $arr['amount'];
}
//Make Final Array -> NB[06-05-2024]
$cats_error_arr = array();
foreach($mi_qry_rslt as $arr){
$mi_id = $arr['mi_id'];
$md = $this->format_float($arr['md']);
$pd = $this->format_float($arr['pd']);
$nb_pd = $this->format_float($arr['nb_pd']);
$w_off = $this->format_float($arr['w_off']);
$ot_hrs = $this->format_float($arr['ot_hrs']);
$ot_days = $this->format_float($arr['ot_days']);
$payroll = (int)($arr['payroll']);
$buff_pd = $this->format_float($arr['buffer_pd']);
$reliev_pd = $this->format_float($arr['reliever_pd']);
$hold_rsn = $arr['hold_reason'];
$stop_pay = $arr['stop_pay_code'];
$pay_st_id = $arr['prime_pay_structure_id'];
$check_val = $this->format_float($pd + $reliev_pd + $nb_pd);
if($md > 31){
$cats_error_arr["md_val_".$mi_id] = "Month days can't be more than 31";
}
if($md <= 0){
$cats_error_arr["md_val_".$mi_id] = "Month days shouldn't be 0 or less";
}
if($pd <= 0 && $payroll === 1){
$cats_error_arr["pd_val_".$mi_id] = "Paid days shouldn't be 0 or less";
}
if($md < $pd){
$cats_error_arr["pd_val_".$mi_id] = "Month days shouldn't be less than paid days";
}
if($md < $check_val){
$cats_error_arr["pd_val_".$mi_id] = "Sum of Paid Days Invalid";
}
if($buff_pd <= 0 && $payroll === 0){
$cats_error_arr["buffer_pd_val_".$mi_id] = "Buffer days shouldn't be 0 or less";
}
if($md < $buff_pd){
$cats_error_arr["buffer_pd_val_".$mi_id] = "Sum of Paid Days Invalid";
}
if($split_arr[$pay_st_id] !== ''){
$ot_drate = $this->format_float($split_arr[$pay_st_id]['ot_drate']);
$ot_hrate = $this->format_float($split_arr[$pay_st_id]['ot_hrate']);
if($ot_drate === "0.00" && $ot_days > 0){ # DOT DAYS VALIDATION BASED ON PAY ST
$cats_error_arr["ot_days_val_".$mi_id] = "OT Days Rate not Available";
}
if($ot_hrate === "0.00" && $ot_hrs > 0){ # DOT HOURS VALIDATION BASED ON PAY ST
$cats_error_arr["ot_hrs_val_".$mi_id] = "OT Hourly Rate not Available";
}
}
if($stop_pay === "Y"){ # STOP PAY
if($hold_rsn === ''){
$cats_error_arr["hold_reason_".$mi_id] = "Required";
}
}
}
echo json_encode(array('success' => true,'cats_error_arr' => $cats_error_arr));
}
//DR CODE FOR GET PAY STRUCTURE DETAILS 22NOV22 END
//BSK START
public function get_employee_details(){
$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);
}
$employee_code = $this->input->post('employee_code');
$table_name = $this->input->post('table_name');
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$search_month = $this->input->post('search_month');
$start_date = date("Y-m-t",strtotime('01-'.$search_month));
$end_date = date("Y-m-d",strtotime('01-'.$search_month));
$logged_area_acc = rtrim($this->logged_area_access,",");
$emp_qry = 'SELECT prime_employees_id,employee_code,emp_name,project_id,wbs_element,wbs_id,wbs_desc,wbs_start_date,cw_employees.position,cw_employees.activity_no,cw_employees.network_id,emp_cost_center_code FROM `cw_employees` inner join cw_sap_wbs on cw_sap_wbs.wbs_id = cw_employees.wbs_element where cw_employees.personal_code in ('.$logged_area_acc.') and cw_employees.trans_status = 1 and employee_code = "'.$employee_code.'" and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, "%Y-%m") >= DATE_FORMAT(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m")))';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_rslt = $emp_info->result();
$emp_info->next_result();
//Get Details
if($emp_rslt){
$emp_id = $emp_rslt[0]->prime_employees_id;
$employee_code = $emp_rslt[0]->employee_code;
$emp_name = $emp_rslt[0]->emp_name;
$emp_wbs_id = $emp_rslt[0]->wbs_id;
$emp_wbs_desc = $emp_rslt[0]->wbs_desc;
$emp_position = $emp_rslt[0]->position;
$emp_act_no = $emp_rslt[0]->activity_no;
$emp_network = $emp_rslt[0]->network_id;
$cost_code = $emp_rslt[0]->emp_cost_center_code;
$wbs_start_date = date("d-m-Y",strtotime($emp_rslt[0]->wbs_start_date));
//FOR TRANSFER
if($table_name === 'transfer_table'){
$prefix = 't_';
$dis = "";
$corporate = false;
}//FOR BILLABLE
else{
$prefix = 'b_';
$dis = "style='display:none'";
$corporate = true;
}
//Check Valid Employee
if(($cost_code === '0' || $cost_code === '') && $corporate){
echo json_encode(array('success' => false,'message' => "Employee should be the Corporate Employee.!",'table_info' => ""));
exit(0);
}
if($cost_code !== '0' && $cost_code !== '' && $table_name === 'transfer_table'){
echo json_encode(array('success' => false,'message' => "Corporate Employees are not Allowed to Transfer.!",'table_info' => ""));
exit(0);
}
$emp_details = "<table class='table table-bordered'>
<thead>
<tr>
<th>Employee Code</th>
<th>Employee Name</th>
</tr>
</thead>
<tbody>
<tr><td>$employee_code</td><td>$emp_name</td></tr>
</tbody>
</table>";
$where_cond = "";
if($table_name === 'transfer_table'){
$where_cond = ' and cw_sap_wbs.wbs_id != "'.$emp_wbs_id.'"';
}
$pay_struct_qry = 'select cw_sap_wbs.prime_sap_wbs_id,cw_sap_wbs.wbs_id,cw_sap_wbs.wbs_desc from cw_sap_wbs inner join cw_pay_structure on cw_pay_structure.wbs_element = cw_sap_wbs.wbs_id where cw_pay_structure.personal_code = "'.$personal_code.'" and cw_sap_wbs.wbs_personal_area_id = "'.$personal_code.'" and cw_sap_wbs.wbs_project_id = "'.$project.'" and date_format(cw_pay_structure.from_date, "%Y-%m") <= date_format(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and date_format(cw_pay_structure.to_date, "%Y-%m") >= date_format(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and cw_sap_wbs.trans_status = 1 and cw_pay_structure.trans_status = 1 and cw_sap_wbs.wbs_start_date <= "'.$start_date.'" and cw_sap_wbs.wbs_end_date >= "'.$end_date.'" '.$where_cond.' GROUP BY cw_sap_wbs.prime_sap_wbs_id';
// and cw_pay_structure.status = 1
$pay_struct_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_struct_qry')");
$pay_struct_rslt = $pay_struct_info->result();
$pay_struct_info->next_result();
if($pay_struct_rslt){
//GET EMPLOYEE WBS ELEMENT FROM MI FMS TABLE
$mi_wbs_qry = 'SELECT wbs_element,position FROM `cw_monthly_input_fms` where cw_monthly_input_fms.employee_code = "'.$employee_code.'" and cw_monthly_input_fms.process_month = "'.$search_month.'" and cw_monthly_input_fms.personal_code = "'.$personal_code.'" and cw_monthly_input_fms.project_id = "'.$project.'" and cw_monthly_input_fms.trans_status = 1';
$mi_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$mi_wbs_qry')");
$mi_wbs_rslt = $mi_wbs_info->result_array();
$mi_wbs_info->next_result();
$mi_wbs_arr = array_column($mi_wbs_rslt ?? [], 'wbs_element', 'wbs_element');
//$mi_pos_arr = array_column($mi_wbs_rslt, 'position', 'position');
$option = "";
if(count($pay_struct_rslt ?? []) > 0){
foreach($pay_struct_rslt as $key => $value){
$prime_sap_wbs_id = $value->prime_sap_wbs_id;
$wbs_id = $value->wbs_id;
$wbs_desc = $value->wbs_desc;
//NEW WBS ONLY LIST FOR THAT EMPLOYEE IN SAME MONTH
if(!$mi_wbs_arr[$wbs_id]){
$option .= "<option data-value = '".$wbs_id."' value = '".$wbs_id."'>".$wbs_desc."</option>";
}
}
$form_input = "<input list='".$prefix."list_wbs_element_$emp_id' name='".$prefix."hid_wbs_element[]' class='form-control' placeholder='Search WBS' size='45' onchange=get_position('$personal_code','$project','$search_month','$employee_code',this.value,'$emp_id','$prefix') onpaste=get_paste('$personal_code','$project','$search_month','$employee_code',event,$emp_id,'$prefix') autocomplete='off'><datalist id='".$prefix."list_wbs_element_$emp_id'>$option</datalist>";
$hidden_input = form_input( array("name"=>"".$prefix."wbs_element[]", "id"=>"".$prefix."wbs_element_$emp_id","value"=>0,"data-val" =>0,"type"=>"hidden"));
$tr_line = "<tr><td style='padding: 6px;'><input type='checkbox' id='".$prefix."trhid_1' name='".$prefix."trhid' class='".$prefix."trhid'></td><td>$employee_code</td><td>$emp_name <input type='hidden' name='".$prefix."emp_id[]' value='$emp_id'> <input type='hidden' name='".$prefix."employee_code[]' value='$employee_code'></td><td $dis>$emp_wbs_id - $emp_wbs_desc <input type='hidden' name='".$prefix."old_wbs[]' value='$emp_wbs_id' data-val='$emp_wbs_desc'><input type='hidden' name='".$prefix."old_position[]' value='$emp_position' data-val='$emp_position'><input type='hidden' name='".$prefix."old_act_no[]' value='$emp_act_no' data-val='$emp_act_no'><input type='hidden' name='".$prefix."old_net_id[]' value='$emp_network' data-val='$emp_network'></td><td>$form_input $hidden_input</td><td><select name='".$prefix."position[]' class='form-control' id='".$prefix."position_$emp_id'><option value=''> --- SELECT Position --- </option></select></td></tr>";
// <td $dis>$wbs_start_date</td>
echo json_encode(array('success' => true,'table_info' => $tr_line,'emp_details' => $emp_details ));
}
}else{
echo json_encode(array('success' => false,'message' => "No Other Pay Structures or WBS Found..!!!",'table_info' => "" ));
}
}else{
echo json_encode(array('success' => false,'message' => "Employee Code not Available or Not Accessible For Your Area Access!!!",'table_info' => "" ));
}
}
//DR CODE FOR wbs based pay struture position value get
public function get_pay_struct(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$search_month = $this->input->post('search_month');
$wbs_element = $this->input->post('wbs_element');
$emp_id = $this->input->post('emp_id');
$employee_code = $this->input->post('employee_code');
$start_date = date("Y-m-t",strtotime('01-'.$search_month));
$end_date = date("Y-m-d",strtotime('01-'.$start_date));
//SELECT EMPLOYEE POSITION QUERY
$mi_wbs_qry = 'SELECT wbs_element,position FROM `cw_monthly_input_fms` where cw_monthly_input_fms.employee_code = "'.$employee_code.'" and cw_monthly_input_fms.process_month = "'.$search_month.'" and cw_monthly_input_fms.personal_code = "'.$personal_code.'" and cw_monthly_input_fms.project_id = "'.$project.'" and cw_monthly_input_fms.trans_status = 1';
$mi_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$mi_wbs_qry')");
$mi_wbs_rslt = $mi_wbs_info->result_array();
$mi_wbs_info->next_result();
//$mi_wbs_arr = array_column($mi_wbs_rslt, 'wbs_element', 'wbs_element');
$mi_pos_arr = array_column($mi_wbs_rslt ?? [], 'position', 'position');
$pos_qry = '';
if(in_array($wbs_element, $mi_wbs_arr ?? [])){
$pos_qry = ' and cw_sap_position.position_code not in ('.implode(",",$mi_pos_arr ?? []).')';
}
// and cw_pay_structure.status = 1 as discussed with porul on 09-dec-2023- he told period alone need to check for pay stgructure.
//pay structure select qry
$pay_position_qry = 'SELECT cw_sap_position.position_code,cw_sap_position.position_name,cw_sap_position.prime_sap_position_id,gross FROM cw_pay_structure inner join cw_sap_position inner join cw_sap_activity on cw_pay_structure.position = cw_sap_position.position_code and cw_pay_structure.activity_no = cw_sap_activity.prime_sap_activity_id WHERE cw_pay_structure.wbs_element = "'.$wbs_element.'" '.$pos_qry.' and cw_pay_structure.trans_status = 1 and date_format(cw_pay_structure.from_date, "%Y-%m") <= date_format(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and date_format(cw_pay_structure.to_date, "%Y-%m") >= date_format(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and cw_sap_position.trans_status = 1 and cw_sap_activity.trans_status = 1 and date_format(cw_sap_activity.act_start_date, "%Y-%m") <= "'.$start_date.'" and date_format(cw_sap_activity.act_end_date, "%Y-%m-%d") >= "'.$end_date.'" GROUP BY cw_sap_position.position_code';
//echo $pay_position_qry; die;
$pay_position_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_position_qry')");
$pay_position_rslt = $pay_position_info->result_array();
$pay_position_info->next_result();
$pos_list = "<option value = ''>---- Select Position ----</option>";
foreach($pay_position_rslt as $for){
$prime_id = $for['prime_id'];
$position = $for['position_code'];
$position_name = $for['position_name'];
$gross = $for['gross'];
$pos_list .= "<option value = '$position'>$position - $position_name - $gross</option>";
}
echo $pos_list;
}
//Save Transfer Details
public function save_transfer(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$post_data = $_POST;
$search_month = $post_data['search_month'];
$personal_code = $post_data['personal_code'];
$project = $post_data['project'];
$type = $post_data['type'];
$emp_codes = $post_data['employee_code'];
$count = count($post_data['emp_id'] ?? []);
$start_date = date("Y-m-t",strtotime('01-'.$search_month));
$end_date = date("Y-m-d",strtotime('01-'.$search_month));
$fin_insert_line = "";
$fin_update_line = "";
//CHECK NEW WBS AND ACT NO START AND END DATE -> NB[11-04-2024]
$sap_wbs_qry = 'SELECT pay.wbs_element,pay.position FROM cw_pay_structure AS pay INNER JOIN cw_sap_wbs AS wbs ON pay.wbs_element = wbs.wbs_id INNER JOIN cw_sap_activity AS act ON act.prime_sap_activity_id = pay.activity_no WHERE pay.status = 1 AND wbs.wbs_start_date <= "'.$start_date.'" and wbs.wbs_end_date >= "'.$end_date.'" AND act.act_start_date <= "'.$start_date.'" and act.act_end_date >= "'.$end_date.'" AND pay.trans_status = 1 AND wbs.trans_status = 1 AND act.trans_status = 1 ';
$sap_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$sap_wbs_qry')");
$sap_wbs_rslt = $sap_wbs_info->result();
$sap_wbs_info->next_result();
$wbs_array = array();
foreach($sap_wbs_rslt as $val){
$wbs_array[$val->position][$val->wbs_element] = $val;
}
//TRANSFER STARTS
$rem_array = ['search_month' => 'search_month','personal_code' => 'personal_code','project' => 'project','type' => 'type'];
$rem_upd_array = ['old_wbs' => 'old_wbs','old_wbs_desc' => 'old_wbs_desc','wbs_element_desc' => 'wbs_element_desc','old_position' => 'old_position','old_act_no' => 'old_act_no','old_net_id' => 'old_net_id'];
for($i = 0;$i < $count;$i++){
$update_line = "";
$insert_line = "";
$insert_key = "";
foreach($post_data as $key => $value){
$value = $value[$i];
if($key === 'employee_code'){
$employee_code = $value;
}
if($key === 'wbs_element'){
$wbs = $value;
}
if($key === 'position'){
$pos = $value;
}
if($key === 'emp_id'){
$key = 'prime_employees_id';
}
if(!$rem_array[$key] && !$rem_upd_array[$key]){
$update_line .= '"'.$value.'",';
}
if(!$rem_array[$key]){
$insert_line .= '"'.$value.'",';
$insert_key .= $key.',';
}
}
if(!$wbs_array[$pos][$wbs]){
echo json_encode(array('success' => FALSE, 'message' => "check start/end date for wbs and activity."));
exit(0);
}
if($insert_line){
$fin_insert_line .= '('.$insert_line.'"'.$search_month.'","'.$personal_code.'","'.$project.'","'.$type.'","'.$this->logged_id.'","'.date("Y-m-d H:i:s").'"),';
$fin_update_line .= '('.$update_line.'"'.$personal_code.'","'.$project.'","'.$this->logged_id.'","'.date("Y-m-d H:i:s").'"),';
}
}
$fin_insert_line = rtrim($fin_insert_line,",");
$fin_update_line = rtrim($fin_update_line,",");
if($update_line){
$prime_update_query = 'INSERT INTO cw_employees (prime_employees_id,employee_code,wbs_element,position,personal_code,project_id,trans_updated_by,trans_updated_date) VALUES '.$fin_update_line.' ON DUPLICATE KEY UPDATE wbs_element = VALUES(wbs_element),position = VALUES(position),personal_code = VALUES(personal_code),project_id = VALUES(project_id),trans_updated_by = VALUES(trans_updated_by),trans_updated_date = VALUES(trans_updated_date)';
$update_info = $this->db->query("CALL sp_a_run ('RUN','$prime_update_query')");
if($update_info){
//Update Paystructure info
if($emp_codes){
$ps_update_query = 'UPDATE cw_employees INNER JOIN cw_pay_structure on (cw_pay_structure.personal_code = cw_employees.personal_code and cw_pay_structure.wbs_element = cw_employees.wbs_element and cw_pay_structure.position = cw_employees.position) inner join cw_sap_wbs on cw_sap_wbs.wbs_id = cw_pay_structure.wbs_element SET cw_employees.personal_code = cw_pay_structure.personal_code,cw_employees.project_id = cw_sap_wbs.wbs_project_id,cw_employees.activity_no = cw_pay_structure.activity_no,cw_employees.network_id = cw_sap_wbs.wbs_network_id,cw_employees.role = cw_pay_structure.category,cw_employees.professional_tax_location = cw_pay_structure.tax_location where cw_employees.employee_code in ("'.trim(implode('","',$emp_codes ?? [])).'") and cw_pay_structure.status = 1 and cw_pay_structure.trans_status = 1';
$ps_update_info = $this->db->query("CALL sp_a_run ('RUN','$ps_update_query')");
if($ps_update_info){
$prime_insert_query = 'INSERT INTO cw_transfer_log ('.$insert_key.'process_month,personal_code,project_id,type,trans_created_by,trans_created_date) values '.$fin_insert_line;
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
echo json_encode(array('success' => TRUE, 'message' => "Successfully Transfered.."));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Please try after Sometime..."));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Please try after Sometime..."));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Please try after Sometime..."));
}
}
}
//BSK END
public function transfer_log(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$search_month = $this->input->post('search_month');
//LOG TABLE CREATE FUNCTION
$tr_line = $this->trans_log_table_create($personal_code,$project,$search_month);
echo json_encode(array('success' => TRUE,'tr_line' => $tr_line));
}
//TRANSFER LOG TABLE CREATION 19NOV22 START
public function trans_log_table_create($personal_code,$project,$search_month){
$transfer_log_qry = 'SELECT cw_transfer_log.*,emp_name FROM `cw_transfer_log` inner join cw_employees on cw_employees.employee_code = cw_transfer_log.employee_code where cw_transfer_log.process_month = "'.$search_month.'" and cw_transfer_log.personal_code = "'.$personal_code.'" and cw_transfer_log.project_id = "'.$project.'" and cw_transfer_log.trans_status = 1 and cw_employees.trans_status = 1';
$transfer_log_info = $this->db->query("CALL sp_a_run ('SELECT','$transfer_log_qry')");
$transfer_log_rslt = $transfer_log_info->result();
$transfer_log_info->next_result();
$tr_line = "<table id='trans_log_table' class='table table table-bordered'>
<thead>
<tr>
<th>Action</th>
<th>Employee Code</th>
<th>Employee Name</th>
<th>Old WBS</th>
<th>New WBS</th>
<th>Position</th>
<th>Type</th>
</tr>
</thead>
<tbody>";
if(count($transfer_log_rslt[0] ?? [])){
foreach($transfer_log_rslt as $key => $value){
$tr_line .= "<tr><td><button class='btn btn-xs btn-danger' id='log_delete_all' style='margin:5px;' onclick = transfer_log_delete('$value->prime_transfer_log_id');><i class='fa fa-trash-o' aria-hidden='true'></i> Delete</button></td><td>$value->employee_code</td><td>$value->emp_name</td><td>$value->old_wbs - $value->old_wbs_desc</td><td>$value->wbs_element - $value->wbs_element_desc</td><td>$value->position</td><td>$value->type</td></tr>\n";
}
}
$tr_line .= "</tbody></table>";
return $tr_line;
}
//TRANSFER LOG TABLE CREATION 19NOV22 END
//TRANSFER LOG DELETE PROCESS 19NOV22 START
public function transfer_log_delete(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$delete_id = $this->input->post('delete_id');
$created_on = date("Y-m-d H:i:s");
$sts = true;
$transfer_log_qry = 'SELECT cw_transfer_log.prime_transfer_log_id,cw_transfer_log.prime_employees_id, cw_transfer_log.employee_code,cw_transfer_log.process_month,cw_transfer_log.personal_code,cw_transfer_log.project_id,cw_transfer_log.old_wbs,cw_transfer_log.old_wbs_desc,cw_transfer_log.old_position,cw_transfer_log.old_act_no,cw_transfer_log.old_net_id,cw_transfer_log.wbs_element,cw_transfer_log.wbs_element_desc,cw_transfer_log.position,cw_transfer_log.type,cw_employees.wbs_element as emp_wbs,cw_employees.position as emp_position FROM `cw_transfer_log` inner join cw_employees on cw_employees.employee_code = cw_transfer_log.employee_code where cw_transfer_log.prime_transfer_log_id = "'.$delete_id.'" and cw_transfer_log.trans_status = 1 and cw_employees.trans_status = 1';
$transfer_log_info = $this->db->query("CALL sp_a_run ('SELECT','$transfer_log_qry')");
$transfer_log_rslt = $transfer_log_info->result();
$transfer_log_info->next_result();
if(count($transfer_log_rslt[0] ?? [])){
$employee_code = $transfer_log_rslt[0]->employee_code;
$process_month = $transfer_log_rslt[0]->process_month;
$personal_code = $transfer_log_rslt[0]->personal_code;
$project_id = $transfer_log_rslt[0]->project_id;
$old_wbs = $transfer_log_rslt[0]->old_wbs;
$old_position = $transfer_log_rslt[0]->old_position;
$old_act_no = $transfer_log_rslt[0]->old_act_no;
$old_net_id = $transfer_log_rslt[0]->old_net_id;
$wbs_element = $transfer_log_rslt[0]->wbs_element;
$position = $transfer_log_rslt[0]->position;
$emp_wbs = $transfer_log_rslt[0]->emp_wbs;
$emp_position = $transfer_log_rslt[0]->emp_position;
if($wbs_element === $emp_wbs && $position === $emp_position){
$emp_upd_qry = 'UPDATE cw_employees SET cw_employees.wbs_element = "'.$old_wbs.'",cw_employees.position = "'.$old_position.'",cw_employees.activity_no = "'.$old_act_no.'",cw_employees.network_id = "'.$old_net_id.'",cw_employees.trans_updated_by = "'.$this->logged_id.'",cw_employees.trans_updated_date = "'.$created_on.'" where cw_employees.employee_code = "'.$employee_code.'" and cw_employees.trans_status = 1';
$emp_upd_info = $this->db->query("CALL sp_a_run ('RUN','$emp_upd_qry')");
if($emp_upd_info){
$log_upd_qry = 'UPDATE cw_transfer_log SET cw_transfer_log.trans_status = 0,cw_transfer_log.trans_deleted_by = "'.$this->logged_id.'",cw_transfer_log.trans_deleted_date = "'.$created_on.'" where cw_transfer_log.prime_transfer_log_id = "'.$delete_id.'" and cw_transfer_log.trans_status = 1';
$log_upd_info = $this->db->query("CALL sp_a_run ('RUN','$log_upd_qry')");
if($log_upd_info){
$mi_del_qry = 'DELETE FROM cw_monthly_input_fms where cw_monthly_input_fms.employee_code = "'.$employee_code.'" and cw_monthly_input_fms.employee_code = "'.$employee_code.'" and cw_monthly_input_fms.process_month = "'.$process_month.'" and cw_monthly_input_fms.personal_code = "'.$personal_code.'" and cw_monthly_input_fms.project_id = "'.$project_id.'" and cw_monthly_input_fms.wbs_element = "'.$wbs_element.'" and cw_monthly_input_fms.position = "'.$position.'" and cw_monthly_input_fms.trans_status = 1';
$mi_del_info = $this->db->query("CALL sp_a_run ('RUN','$mi_del_qry')");
$sts = TRUE;
$msg = "Success";
}
}else{
$sts = FALSE;
$msg = "Please try after Sometime...";
}
}else{
$sts = FALSE;
$msg = "WBS and Position should verify from Employee Master.!";
}
}else{
$sts = FALSE;
$msg = "No Data Found..!";
}
//LOG TABLE CREATE FUNCTION
$tr_line = $this->trans_log_table_create($personal_code,$project_id,$process_month);
echo json_encode(array('success' => $sts, 'message' => $msg,'tr_line' => $tr_line));
}
//TRANSFER LOG DELETE PROCESS 19NOV22 END
public function combination_exist(){
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$search_month = $this->input->post('search_month');
$timesheet_qry = 'SELECT count(*) as count FROM cw_monthly_input_fms WHERE personal_code = "'.$personal_code.'" AND project_id = "'.$project.'" AND process_month = "'.$search_month.'" AND trans_status = 1';
$timesheet_info = $this->db->query("CALL sp_a_run ('SELECT','$timesheet_qry')");
$timesheet_rslt = $timesheet_info->result();
$timesheet_info->next_result();
$combination = (int)$timesheet_rslt[0]->count;
if($combination > 0){
echo json_encode(array('success'=>true,'message'=>'combination exist.!'));
}else{
echo json_encode(array('success'=>false));
}
}
public function export_to_excel(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$personal_code = $this->input->post('personal_code');
$project_id = $this->input->post('project_id');
$search_month = $this->input->post('search_month');
$start_date = date("Y-m-t",strtotime('01-'.$search_month));
$end_date = date("Y-m-d",strtotime('01-'.$search_month));
$filter_wbs_arr = $this->input->post('filter_wbs');
// $filter_wbs_str = implode(",",$filter_wbs_arr);
$filter_wbs = str_replace(',','","',$filter_wbs_arr);
$pay_struct_rslt = $this->pay_structure_qry_fun($search_month,$personal_code,$project_id,$start_date,$end_date);
$grp_wbs = str_replace(',','","',$pay_struct_rslt[0]->wbs_element);
$grp_position = str_replace(',','","',$pay_struct_rslt[0]->position);
if($filter_wbs !== "null" && $filter_wbs !== ""){
$filter_qry = 'and cw_monthly_input_fms.wbs_element in ("'.$filter_wbs.'")';
}
//QRY FOR GET A FORM SETTING BASED EMPLOYEE MASTER PAYROLL COLUMNS(INPUTS)
$form_qry = 'SELECT * FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = "1" AND FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) AND (earn_month_check = "1" OR deduction_month_check = "1") AND label_name NOT IN ("role","process_month","date_of_joining","date_of_birth","w_off","stop_pay_code","hold_reason","termination_status","sub_date","msal_apr_date","msal_rej_date","chk_date") ORDER BY monthly_input_sort';
$form_info = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_info_rslt = $form_info->result();
$form_info->next_result();
$label = "";
foreach($form_info_rslt as $val){
$label_id = $val->label_name;
if($label_id === 'activity_no'){
$label_id = 'cw_sap_activity.act_no as activity_no,';
}else{
$label_id = "cw_monthly_input_fms.$label_id, ";
}
$label .= $label_id ;
}
$search_query .= "SELECT $label cw_sap_position.position_name as pro_desc,cw_pay_structure.gross FROM cw_monthly_input_fms inner join cw_employees on cw_employees.employee_code = cw_monthly_input_fms.employee_code inner join cw_sap_activity on cw_monthly_input_fms.activity_no = cw_sap_activity.prime_sap_activity_id and cw_sap_activity.act_start_date <= \"".$start_date."\" and cw_sap_activity.act_end_date >= \"".$end_date."\" inner join cw_sap_position on cw_sap_position.position_code = cw_monthly_input_fms.position inner join cw_pay_structure on (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 and cw_pay_structure.category = cw_monthly_input_fms.role and date_format(cw_pay_structure.from_date, '%Y-%m') <= date_format(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m') and date_format(cw_pay_structure.to_date, '%Y-%m') >= date_format(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m')) where cw_monthly_input_fms.trans_status = 1 and cw_monthly_input_fms.process_month=\"".$search_month."\" ".$filter_qry." and cw_monthly_input_fms.personal_code = \"".$personal_code."\" and cw_monthly_input_fms.project_id = \"".$project_id."\" and cw_monthly_input_fms.wbs_element in (\"".$grp_wbs."\") and cw_monthly_input_fms.position in (\"".$grp_position."\") and cw_pay_structure.trans_status = 1 and DATE_FORMAT(cw_employees.date_of_joining, '%Y-%m') <= DATE_FORMAT(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m') and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, '%Y-%m') >= DATE_FORMAT(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m'))) and cw_employees.entry_flag = 'e' and cw_monthly_input_fms.entry_status in (0,2) ORDER BY cw_monthly_input_fms.prime_monthly_input_fms_id ASC ";
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
# Replace viewname.
$lable_qry = 'SELECT label_name, view_name, field_type FROM cw_form_setting WHERE prime_module_id IN ("employees","sap_project","sap_activity","pay_structure")';
$label_info = $this->db->query("CALL sp_a_run ('SELECT', '$lable_qry')");
$label_rslt = $label_info->result_array();
$label_info->next_result();
$newArray = array();
foreach($label_rslt as $val){
$viewName = $val['label_name'];
$newArray[$viewName] = $val;
}
if($search_result){
echo json_encode(array('success' => true,'search_result'=>$search_result,'form_rslt_data'=>$newArray));
}
}
//FOR REMOVE LAST TWO COLUMNS IN EXCEL -> PRO DESC AND GROSS.
public function excelColumnLetterToNumber($columnLetter){
$columnLetter = strtoupper($columnLetter);
$length = strlen($columnLetter);
$number = 0;
for($i = 0; $i < $length; $i++){
$number += (ord($columnLetter[$i]) - ord('A') + 1) * pow(26, $length - $i - 1);
}
return $number;
}
public function excelColumnNumberToLetter($columnNumber){
$columnLetter = '';
while($columnNumber > 0){
$remainder = ($columnNumber - 1) % 26;
$columnLetter = chr(65 + $remainder) . $columnLetter;
$columnNumber = ($columnNumber - $remainder - 1) / 26;
}
return $columnLetter;
}
//FUNTION FOR CONVERTING VALUES
public function format_float($value){
#If value is decimal then .2 allow else int
if(preg_match('/^\d*\.?\d*$/', $value)){
return number_format((float)$value, 2, '.', '');
}else{
return (int)$value;
}
}
}
?>