File: /home/cafsindia/uds.cafsinfotech.in/application/controllers_bk/Arrear_process.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Arrear_process extends Action_controller{
public function __construct(){
parent::__construct('arrear_process');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//PAGE INFO FUNCTION
$this->page_info();
$data['encKey'] = $this->generateKey();
$data['module_sts'] = (int)$this->module_sts;
$data['pick_list'] = $this->pick_list;
$data['form_info'] = $this->form_info;
$data['table_head'] = $this->table_head;
//PROJECT ID GET BASED ON ARREAR INFO TABLE
if((int) $this->logged_user_role === 3 || (int) $this->logged_user_role === 1){
$arr_pro_id_qry = 'SELECT prime_sap_project_id,pro_id,pro_desc FROM cw_sap_project INNER JOIN cw_arr_info ON cw_arr_info.project_id = cw_sap_project.pro_id WHERE cw_sap_project.trans_status = 1 AND FIND_IN_SET(cw_sap_project.pro_personal_area_id, "'.$this->logged_area_access.'") AND cw_arr_info.arr_status <> 1 AND cw_arr_info.entry_status <> 1 AND cw_arr_info.trans_status = 1 GROUP BY cw_arr_info.project_id';
}else{
$arr_pro_id_qry = 'SELECT prime_sap_project_id,pro_id,pro_desc FROM cw_sap_project INNER JOIN cw_arr_info on cw_arr_info.project_id = cw_sap_project.pro_id INNER JOIN cw_arr_transactions ON cw_arr_transactions.prime_arr_info_id = cw_arr_info.prime_arr_info_id WHERE cw_sap_project.trans_status = 1 AND FIND_IN_SET(cw_sap_project.pro_personal_area_id, "'.$this->logged_area_access.'") and cw_arr_info.trans_status = 1 AND cw_arr_transactions.check_status = 0 AND cw_arr_transactions.entry_status = 1 GROUP BY cw_arr_info.project_id';
}
$arr_pro_id_data = $this->db->query("CALL sp_a_run ('SELECT','$arr_pro_id_qry')");
$arr_pro_id_rslt = $arr_pro_id_data->result_array();
$arr_pro_id_data->next_result();
//PROJECT DESCRIPTION ARRAY
$pro_id_list = "";
$pro_id_desc = array();
foreach($arr_pro_id_rslt as $for){
$prime_id = $for['prime_sap_project_id'];
$pro_id = $for['pro_id'];
$pro_desc = $for['pro_desc'];
$pro_id_list .= "<option data-value='$pro_id' value='$pro_id'> $pro_desc </option>";
$pro_id_desc[$pro_id] = $pro_desc;
}
$data['project_list'] = $pro_id_list;
//AAREAR TABLE REALTED POSITION START 11OCT22
$this->load->view("$this->control_name/manage",$data);
}
//FUNTION FOR WBS ELEMENT BASED ON PR0JECT ID -> NB [19AUG23]
public function get_wbs(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$project_id = $this->input->post('project_id');
if((int) $this->logged_user_role === 3 || (int) $this->logged_user_role === 1){
$get_wbs_qry = 'SELECT DISTINCT cw_sap_wbs.wbs_id,cw_sap_wbs.prime_sap_wbs_id,cw_sap_wbs.wbs_desc FROM cw_arr_info INNER JOIN cw_sap_wbs ON cw_sap_wbs.wbs_id = cw_arr_info.wbs_element WHERE cw_arr_info.trans_status = 1 AND cw_sap_wbs.trans_status = 1 AND cw_arr_info.project_id = "'.$project_id.'" AND FIND_IN_SET(cw_sap_wbs.wbs_personal_area_id, "'.$this->logged_area_access.'") AND cw_arr_info.arr_status <> 1 AND cw_arr_info.entry_status <> 1'; // GROUP BY cw_arr_info.wbs_element
}else{
$get_wbs_qry = 'SELECT DISTINCT cw_sap_wbs.wbs_id,cw_sap_wbs.prime_sap_wbs_id,cw_sap_wbs.wbs_desc FROM cw_arr_info INNER JOIN cw_sap_wbs ON cw_sap_wbs.wbs_id = cw_arr_info.wbs_element INNER JOIN cw_arr_transactions ON cw_arr_transactions.prime_arr_info_id = cw_arr_info.prime_arr_info_id WHERE cw_arr_info.trans_status = 1 AND cw_sap_wbs.trans_status = 1 AND cw_arr_info.project_id = "'.$project_id.'" AND FIND_IN_SET(cw_sap_wbs.wbs_personal_area_id, "'.$this->logged_area_access.'") AND cw_arr_transactions.check_status = 0 AND cw_arr_transactions.entry_status = 1'; // GROUP BY cw_arr_info.wbs_element
}
$get_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$get_wbs_qry')");
$get_wbs_rslt = $get_wbs_info->result_array();
$get_wbs_info->next_result();
if($get_wbs_rslt){
echo json_encode(array("success"=>TRUE,"wbs"=>$get_wbs_rslt));
}else{
echo json_encode(array("success"=>FALSE,"message"=>"No WBS Element For this project ID.!"));
}
}
public function get_month(){
$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);
}
$project_id = $this->input->post('project_id');
$from_wbs = $this->input->post('from_wbs');
$to_wbs = $this->input->post('to_wbs');
if((int) $this->logged_user_role === 3 || (int) $this->logged_user_role === 1){
$get_wbs_qry = 'SELECT cw_arr_info.prime_arr_info_id,cw_arr_info.arr_from_date as arr_from,cw_arr_info.arr_to_date as arr_to,cw_arr_info.wbs_element FROM cw_arr_info INNER JOIN cw_sap_wbs ON cw_sap_wbs.wbs_id = cw_arr_info.wbs_element WHERE cw_arr_info.trans_status = 1 AND cw_sap_wbs.trans_status = 1 AND cw_arr_info.project_id = "'.$project_id.'" AND cw_arr_info.entry_status <> 1 AND cw_arr_info.arr_status <> 1 AND (cw_arr_info.wbs_element = "'.$from_wbs.'" OR cw_arr_info.wbs_element = "'.$to_wbs.'")';
}else{
$get_wbs_qry = 'SELECT cw_arr_info.prime_arr_info_id,arr_from,arr_to,cw_arr_info.wbs_element FROM cw_arr_info INNER JOIN cw_sap_wbs ON cw_sap_wbs.wbs_id = cw_arr_info.wbs_element INNER JOIN cw_arr_transactions ON cw_arr_transactions.prime_arr_info_id = cw_arr_info.prime_arr_info_id WHERE cw_arr_info.trans_status = 1 AND cw_sap_wbs.trans_status = 1 AND cw_arr_info.project_id = "'.$project_id.'"AND cw_arr_transactions.check_status = 0 AND cw_arr_transactions.entry_status = 1 AND (cw_arr_info.wbs_element = "'.$from_wbs.'" OR cw_arr_info.wbs_element = "'.$to_wbs.'") ';
}
$get_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$get_wbs_qry')");
$get_wbs_rslt = $get_wbs_info->result_array();
$get_wbs_info->next_result();
foreach($get_wbs_rslt as &$dateRange){
$fromDate = DateTime::createFromFormat('m-Y', $dateRange['arr_from']);
$toDate = DateTime::createFromFormat('m-Y', $dateRange['arr_to']);
$dateRange['arr_from'] = $fromDate->format('M-Y');
$dateRange['arr_to'] = $toDate->format('M-Y');
}
if($get_wbs_rslt){
echo json_encode(array("success"=>TRUE,"wbs"=>$get_wbs_rslt));
}else{
echo json_encode(array("success"=>FALSE,"message"=>"No Records Found.!"));
}
}
public function get_position(){
$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);
}
$arr_info_id = $this->input->post('prime_info');
if((int) $this->logged_user_role === 3 || (int) $this->logged_user_role === 1){
$get_pos_qry = 'SELECT cw_sap_position.position_code,cw_sap_position.position_name,cw_sap_position.prime_sap_position_id FROM cw_arr_info INNER JOIN cw_sap_position ON cw_sap_position.position_code = cw_arr_info.position WHERE cw_arr_info.trans_status = 1 AND cw_sap_position.trans_status = 1 AND prime_arr_info_id = "'.$arr_info_id.'" AND cw_arr_info.entry_status <> 1 AND cw_arr_info.arr_status <> 1 GROUP BY cw_arr_info.position';
}else{
$get_pos_qry = 'SELECT cw_sap_position.position_code,cw_sap_position.position_name,cw_sap_position.prime_sap_position_id FROM cw_arr_info INNER JOIN cw_sap_position ON cw_sap_position.position_code = cw_arr_info.position INNER JOIN cw_arr_transactions ON cw_arr_transactions.prime_arr_info_id = cw_arr_info.prime_arr_info_id WHERE cw_arr_info.trans_status = 1 AND cw_sap_position.trans_status = 1 AND cw_arr_info.prime_arr_info_id = "'.$arr_info_id.'" AND cw_arr_transactions.check_status = 0 AND cw_arr_transactions.entry_status = 1 GROUP BY cw_arr_info.position';
}
$get_pos_info = $this->db->query("CALL sp_a_run ('SELECT','$get_pos_qry')");
$get_pos_rslt = $get_pos_info->result_array();
$get_pos_info->next_result();
$pos_list = "";
foreach($get_pos_rslt as $for){
$prime_id = $for['prime_sap_position_id'];
$pos_id = $for['position_code'];
$pos_desc = $for['position_name'];
$pos_list .= "<option data-value='$pos_id' value='$pos_id'> $pos_desc </option>";
}
echo json_encode(array("success"=>true,"position_list"=>$pos_list));
}
//FUNCTION FOR GET A ARREAR REPORT DATA ON 30NOV22 START
public function arrear_process(){
$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);
}
$project_id = $this->input->post('project_id');
$from_wbs = $this->input->post('from_wbs');
$to_wbs = $this->input->post('to_wbs');
$id = $this->query_arr_trans($this->input->post('month'));
$from_date = $id[0]->arr_from;
$to_date = $id[0]->arr_to;
$position = $this->input->post('position');
$process_type = $this->input->post('process_type');
$all_prime_ids = [];
if($process_type === "process"){ //process
$process_mode = 1;
}else{//Reprocess(table reprocess button)
$process_mode = 2;
}
return $this->arrear_process_tbl($project_id,$from_wbs,$to_wbs,$from_date,$to_date,$position,$process_mode,$all_prime_ids);
}
public function arrear_process_tbl($project_id,$from_wbs,$to_wbs,$from_date,$to_date,$position,$process_mode,$all_prime_ids){
# For php.
if($position){
$position_qry = 'and cw_arr_transactions.position="'.$position.'" ';
}
# For Procedure.
if(!$position){
$position = 'null';
}
$from_date = date("Y-m-d",strtotime('01-'.$from_date));
$to_date = date("Y-m-t",strtotime('01-'.$to_date));
$from_date_check = date("M", strtotime($from_date)) . date("y", strtotime($from_date));
$all_prime_ids = '"'.implode('","',$all_prime_ids ?? []).'"';
//GET A FROM AND TO MONTH BETWEEN ALL MONTH PERIOD START
$mon_year_priod = [];
$start = $from_date;
$last = date('M-y', strtotime($to_date));
do {
$month = date('M-y', strtotime($start));
$mon_year_priod[$month] = date('My', strtotime($start));
$start = date("Y-m-d",strtotime('+1 month', strtotime($start)));
} while ($month != $last);
//GET A FROM AND TO MONTH BETWEEN ALL MONTH PERIOD END
//FOR GET MI EARNINGS AND DEDUCTION INPUTS NAME
// ORDER BY monthly_input_sort asc
$mi_inp_sel_qry = 'SELECT cw_form_setting.label_name,cw_form_setting.view_name FROM cw_form_setting WHERE prime_module_id IN ("employees") AND field_show = "1" AND trans_status = "1" AND (earn_month_check = "1" OR deduction_month_check = "1") AND cw_form_setting.input_for IN ("34","49") ORDER BY cw_form_setting.field_sort ASC';
$mi_inp_sel_info = $this->db->query("CALL sp_a_run ('SELECT','$mi_inp_sel_qry')");
$mi_inp_sel_rslt = $mi_inp_sel_info->result_array();
$mi_inp_sel_info->next_result();
$mi_inp_sel_arr = array_column($mi_inp_sel_rslt ?? [],"label_name","label_name");
//mi inputs and month and year array concatenate
$inp_mon_year_arr = array();
foreach($mi_inp_sel_arr as $inp_val){
foreach($mon_year_priod as $val){
$inp_mon_year_arr[$inp_val."-".$val] = $inp_val."-".$val;
}
}
//PAY STRUCTURE SELECTED COLUMNS GET
$db_name = $this->config->item("db_name");
$pay_arr_col_qry = 'SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ="'.$db_name.'" AND TABLE_NAME = "cw_arr_transactions" and COLUMN_NAME not in ("prime_arr_transactions_id", "prime_arr_info_id", "employee_code", "employee_name", "project_id", "project_desc", "wbs_element", "wbs_desc", "position", "pos_desc", "rev_position", "rev_pos_desc", "pay_struct_position", "pay_struct_pos_desc","arr_status","trans_created_by","trans_created_date","trans_updated_by","trans_updated_date","trans_deleted_by","trans_deleted_date","trans_status","sap_doc_no","mb_doc_number","payment_doc_number","sap_trans_status","sap_remarks","utr_no","approved_date","rejected_date","check_status","input_check","arr_ybonus","entry_status","arr_gross")';
$pay_arr_col_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_arr_col_qry')");
$pay_arr_col_rslt = $pay_arr_col_info->result_array();
$pay_arr_col_info->next_result();
$pay_arr_col_arr = array_column($pay_arr_col_rslt ?? [],"col_name","col_name");
//SP CALL FOR ARREAR CALCULATION RESULT GET
if((int)$process_mode === 1){ //process
if((int) $this->logged_user_role === 3 || (int) $this->logged_user_role === 1){ //maker data
$process_type = "process";
$save_arr_info = $this->db->query("CALL itsp_Arrear('$project_id','$from_wbs','$to_wbs','$from_date','$to_date','$position','$process_type')");
}else{ //checker data
$process_type = "view";
$sel_arr_qry = 'SELECT cw_arr_transactions.arr_status as arr_sts,cw_arr_transactions.*,cw_arr_transaction_days.*,cw_arr_info.*,cw_employees.termination_status from cw_arr_transactions INNER JOIN cw_arr_transaction_days on cw_arr_transaction_days.prime_arr_transactions_id = cw_arr_transactions.prime_arr_transactions_id INNER JOIN cw_arr_info ON cw_arr_info.prime_arr_info_id = cw_arr_transactions.prime_arr_info_id INNER JOIN cw_employees ON cw_arr_transactions.employee_code = cw_employees.employee_code WHERE cw_arr_transactions.wbs_element <= "'.$from_wbs.'" AND cw_arr_transactions.wbs_element >= "'.$to_wbs.'" AND cw_arr_transaction_days.proc_date >= "'.$from_date.'" AND cw_arr_transaction_days.proc_date <= "'.$to_date.'" '.$position_qry.' AND cw_arr_transactions.trans_status=1 AND cw_arr_transactions.entry_status = 1 AND check_status = 0 AND cw_arr_info.trans_status = 1 GROUP BY cw_arr_transactions.prime_arr_transactions_id';
$save_arr_info = $this->db->query("CALL sp_a_run ('SELECT','$sel_arr_qry')");
}
}else
if((int)$process_mode === 2){ // Reprocess
$process_type = "reprocess";
$save_arr_info = $this->db->query("CALL itsp_Arrear('$project_id','$from_wbs','$to_wbs','$from_date','$to_date','$position','$process_type')");
}else
if((int)$process_mode === 3){ // maker Submit
$sel_arr_qry = 'select * from cw_arr_transactions where prime_arr_transactions_id in('.$all_prime_ids.') and trans_status = 1 and check_status != 1';
$save_arr_info = $this->db->query("CALL sp_a_run ('SELECT','$sel_arr_qry')");
}else{ //checker approved or rejected
$sel_arr_qry = 'SELECT cw_arr_transactions.arr_status as arr_sts,cw_arr_transactions.*,cw_employees.termination_status FROM cw_arr_transactions JOIN cw_employees ON cw_arr_transactions.employee_code = cw_employees.employee_code WHERE prime_arr_transactions_id IN ('.$all_prime_ids.') AND entry_status = 1 AND check_status = 0 AND cw_arr_transactions.trans_status = 1';
$save_arr_info = $this->db->query("CALL sp_a_run ('SELECT','$sel_arr_qry')");
}
$save_arr_rslt = $save_arr_info->result_array();
$save_arr_info->next_result();
$table_info = '';
if($save_arr_rslt[0]['result']){
echo json_encode(array('success' => FALSE, 'message' => 'No data Available..!','table_info' => $table_info));
exit(0);
}else{
//THIS IS ARREAR RESULT TABE CREATE ARRAY
$arr_rslt_arr = array();
array_walk($save_arr_rslt ?? [], function($v, $k) use(&$arr_rslt_arr) {
$arr_rslt_arr[$v["prime_arr_transactions_id"]] = $v;
});
//array for get arrear transaction id for using below select query
$arr_trans_id_arr = implode('","',array_map(function($v){
return $v['prime_arr_transactions_id'] = $v['prime_arr_transactions_id'];
}, $save_arr_rslt ?? []) ?? []);
$arr_trans_days_qry = 'SELECT * FROM cw_arr_transaction_days WHERE cw_arr_transaction_days.prime_arr_transactions_id in ("'.$arr_trans_id_arr.'") and cw_arr_transaction_days.trans_status = 1 GROUP BY cw_arr_transaction_days.prime_arr_transactions_id,cw_arr_transaction_days.arr_month,cw_arr_transaction_days.arr_year,cw_arr_transaction_days.arr_input ORDER BY cw_arr_transaction_days.prime_arr_transaction_days_id ASC';
$arr_trans_days_info = $this->db->query("CALL sp_a_run ('SELECT','$arr_trans_days_qry')");
$arr_trans_days_rslt = $arr_trans_days_info->result_array();
$arr_trans_days_info->next_result();
if(!$arr_trans_days_rslt){
$table_info = "";
if($process_mode === 1 || $process_mode === 2){
echo json_encode(array('success' => FALSE, 'message' => 'No Records Found..!','table_info' => $table_info));
exit(0);
}else{
return $table_info;
}
}
array_walk($arr_trans_days_rslt, function($v, $k) use (&$arr_rslt_arr){
$arr_trans_id = $v["prime_arr_transactions_id"];
$arr_input = $v["arr_input"];
$arr_month = substr($v["arr_month"], 0, 3);
$arr_year = date("y",strtotime("01-01-".$v["arr_year"]));
$arr_key = $arr_input."-".$arr_month.$arr_year;
if($arr_rslt_arr[$arr_trans_id]){
$arr_rslt_arr[$arr_trans_id][$arr_key] = $v["arr_value"];
}
});
$arr_days_rslt = array();
foreach($arr_trans_days_rslt as $value){
$month = substr($value["arr_month"], 0, 3);
$year = substr($value["arr_year"], -2);
$month_year = $month . $year;
$arr_days_rslt[$value['prime_arr_transactions_id']][$month_year][$value['arr_input']] = $value['prime_arr_transaction_days_id'];
$arr_input_rslt[$value['prime_arr_transactions_id']][$month_year][$value['arr_input']] = $value['arr_value'];
}
//arr_transaction_days input(for editable input)
$trans_days_col_arr= array("ex_days","ex_hrs","ot_days","ot_hrs","pd","md","spl_ot_hours");
// -------------- TABLE CREATION PROCESS ----------------------------
//HEADER
$table_head = "<tr><th><input type='checkbox' name='select_all' class='select_all'></th><th>ARREAR FROM</th><th>ARREAR TO</th><th>ARREAR STATUS</th><th>EMPLOYEE CODE</th><th>EMPLOYEE NAME</th><th>EMPLOYEE STATUS</th><th>POSITION NO</th><th>POSITION DESC</th><th>REVISED POSITION NO</th><th>REVISED POSITION NAME</th><th>PROJECT</th><th>WBS ELEMENT</th><th>WBS ELEMENT DESC</th>";
$arr_head = "";
$other_head = "";
$table_body = "";
$count = 1;
foreach($arr_rslt_arr as $key => $arr_val){
$arr_body = "";
$other_body = "";
$md_id = "";
//BASIC DATA
$prime_id = $arr_val["prime_arr_transactions_id"];
$employee_code = $arr_val["employee_code"];
$employee_name = $arr_val["employee_name"];
$pos_no = $arr_val["position"];
$pos_desc = $arr_val["pos_desc"];
$rev_pos_no = $arr_val["rev_position"];
$rev_pos_desc = $arr_val["rev_pos_desc"];
$project = $arr_val["project_desc"];
$arr_from = $arr_val["arr_from"];
$arr_to = $arr_val["arr_to"];
$wbs = $arr_val["wbs_element"];
$wbs_desc = $arr_val["wbs_desc"];
$termination_sts = $arr_val["termination_status"];
$termi_sts = $arr_val["termi_sts"];
$entry_status = $arr_val["entry_status"];
$input_check = (int)$arr_val["input_check"];
$arr_mbonus_grs = $arr_val["arr_mbonus_grs"];
$tr_id = $employee_code.$prime_id;
if((int) $this->logged_user_role === 3 || (int) $this->logged_user_role === 1){
$arr_status = (int)$arr_val["arr_status"];
if((int)$termi_sts === 0){
$chk_box = " checked ";
$emp_sts = " Active ";
}else{
$chk_box = "";
$emp_sts = " In Active ";
}
}else{
$arr_status = (int)$arr_val["arr_sts"];
if((int)$termination_sts === 0){
$emp_sts = " Active ";
}else{
$emp_sts = " In Active ";
}
$chk_box = $input_check === 1 ? "checked" : "";
}
if($arr_status === 4){
$arr_sts = "Verified";
$arr_style = "style = 'color: #00ABF0; font-weight:800;'";
}else if($arr_status === 3){
$arr_sts = "Rejected";
$arr_style = "style = 'color: red;'";
}else if($arr_status === 1){
$arr_sts = "Approved";
$arr_style = "style = 'color: green;'";
}else{
$arr_sts = "Pending";
$arr_style = "style = 'color: orange;'";
}
if((int)$entry_status === 2 && ((int)$process_mode === 1 || (int)$process_mode === 2)){
$style = "style='background-color: #D5F4E6;'";
}else{
$style = "";
}
$table_body .= "<tr id='$tr_id' $style><input type='hidden' class='hidden-input' value='$prime_id'><td class='exclude-export'><input type='checkbox' class='sel_chkbox' value='$prime_id' $chk_box></td><td>$arr_from</td><td>$arr_to</td><td $arr_style>$arr_sts</td><td>$employee_code</td><td>$employee_name</td><td>$emp_sts</td><td>$pos_no</td><td>$pos_desc</td><td>$rev_pos_no</td><td>$rev_pos_desc</td><td>$project</td><td>$wbs</td><td>$wbs_desc</td>";
//EXIST REVISE ARREAR CALCUALTED DATA TABLE ADD
foreach($pay_arr_col_arr as $pay_val){
$inp_head = strtoupper(str_replace("_"," ",$pay_val));
$inp_amt = $arr_val[$pay_val];
if(!$inp_amt){
$inp_amt = 0.00;
}
$pay_str_arr = explode("_", $pay_val ?? "");
$pay_check = $pay_str_arr[0];
//EXIST REVISE DIFFERENCE BODY DATA
if($pay_check === "exist" || $pay_check === "rev" || $pay_check === "diff"){
if($count === 1){
$table_head .= "<th>$inp_head</th>";
}
$table_body .= "<td>$inp_amt</td>";
}else
if($pay_check === "arr" && $pay_val !== "arr_adv"){ //EARNINGS BODY DATA
if($count === 1){
$arr_head .= "<th>$inp_head</th>";
}
$arr_body .= "<td>$inp_amt</td>";
}else{
if($pay_val !== "entry_status" && $pay_val !== 'arr_adv'){ //OTHER BODY DATA
if($count === 1){
$other_head .= "<th>$inp_head</th>";
}
$other_body .= "<td>$inp_amt</td>";
}
//ARR ADVANCE
if($pay_val === "arr_adv"){
$other_head .= "<th>ARR ADV</th>";
$arr_adv_id = $pay_val . "_" . $prime_id;
if((int) $this->logged_user_role === 3 || (int) $this->logged_user_role === 1){
$other_body .= "<td><input type='text' oninput='update_arr_adv($prime_id,this.value,this.id,$tr_id)' name='input_field' id=".$arr_adv_id." value='$inp_amt' class='form-control editable'></td>";
}else{
$other_body .= "<td>$inp_amt</td>";
}
}
}
}
if($arr_mbonus_grs){
$other_head .= "<th>ARR MBONUS GRS</th>";
$other_body .= "<td>$arr_mbonus_grs</td>";
}
//MI INPUT NAME WITH SERACH MONTH AND YEAR BODY DATA CREATE START
foreach($inp_mon_year_arr as $inp_mon_val){
$inp_id_val = str_replace("-","_",$inp_mon_val);
if($arr_val[$inp_mon_val]){
$inp_mon_year_val = $arr_val[$inp_mon_val];
}else{
$inp_mon_year_val = 0;
}
$inp_mon_year_head = strtoupper(str_replace("_", " ", $inp_mon_val));
$final_pos_arr = explode("-", $inp_mon_val ?? "");
$col_name = $final_pos_arr[0];
$month_year = end($final_pos_arr);
//HEADER
if($count === 1){
$table_head .= "<th>$inp_mon_year_head</th>";
}
//arr_trans_days input
$input_name = rtrim($inp_id_val,"_".$month_year);
if($from_date_check === $month_year && in_array($input_name,$trans_days_col_arr) && $input_name !== "md"){
$trans_id = $arr_days_rslt[$prime_id][$month_year][$input_name];
$inp_mon_year_val = $arr_input_rslt[$prime_id][$month_year][$input_name];
$inp_id = $inp_id_val."_".$trans_id;
if($inp_mon_year_val <= 0){
$inp_mon_year_val = 0;
}
if((int) $this->logged_user_role === 3 || (int) $this->logged_user_role === 1){
$table_body .= "<td><input type='text' oninput='table_update($trans_id,this.value,this.id,$prime_id,$inp_mon_year_val,$tr_id)' name='input_field' id=".$inp_id." value='$inp_mon_year_val' class='form-control editable'></td>";
}else{
$table_body .= "<td>$inp_mon_year_val</td>";
}
}else{
$table_body .= "<td>$inp_mon_year_val</td>";
}
}
//LOOP MI INPUT NAME WITH SERACH MONTH AND YEAR BODY DATA CREATE END
//HEADER
if($count === 1){
$table_head = $table_head.$arr_head.$other_head."</tr>";
}
$table_body = $table_body.$arr_body.$other_body."</tr>";
$count++;
}
//TABLE CREATE
$table_info = "<table class = 'table table-hover' id = 'arr_process_table' style = 'white-space: nowrap;margin-top:11px;'><thead>$table_head</thead><tbody>$table_body</tbody></table>";
//RESPONSE DATA
if((int)$process_mode === 2){
$msg = "Arrear Reprocessed Successfully..!";
}else
if((int)$process_mode === 4){
$msg = "Arrear Approved Successfully..!";
}else
if((int)$process_mode === 5){
$msg = "Arrear Rejected Successfully..!";
}else{
$msg = "Arrear Processed Successfully..!";
}
//|| (int)$process_mode === 6
//3- reprocess, 4-approve,5-rejected
if((int)$process_mode === 3 || (int)$process_mode === 4 || (int)$process_mode === 5 || (int)$entry_status === 5){
return $table_info;
}else{
echo json_encode(array('success' => TRUE, 'message' =>$msg,'table_info' => $table_info,'process_mode'=>$process_mode));
exit(0);
}
}
}
//update value in arr_transaction_days table
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);
}
$trans_id = $this->input->post('trans_id');
$value = $this->input->post('value');
$arr_trans_id = $this->input->post('arr_trans_id');
$created_on = date("Y-m-d h:i:s");
$update_query = 'UPDATE cw_arr_transaction_days SET arr_value="'.$value.'",trans_updated_date = "'.$created_on.'",trans_updated_by= "'.$this->logged_id.'" WHERE prime_arr_transaction_days_id="'.$trans_id.'" AND trans_status =1';
$upd_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
if($upd_rslt){
//UPDATE ENTRY STATUS=2 WHEN CHANGE INPUT
$update_arr_trans = 'UPDATE cw_arr_transactions SET entry_status = 2,trans_updated_date = "'.$created_on.'",trans_updated_by= "'.$this->logged_id.'" WHERE prime_arr_transactions_id ="'.$arr_trans_id.'" AND trans_status=1';
$update_arr_trans_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$update_arr_trans')");
echo json_encode(array('success' => TRUE, 'message' => 'Successfully Updated'));
}else{
echo json_encode(array('success' => false, 'message' => 'Not Updated'));
}
}
//for maker submit
public function update_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);
}
$prime_ids = '"'.implode('","',$this->input->post('prime_ids') ?? []).'"';
$project_id = $this->input->post('project_id');
$from_wbs = $this->input->post('from_wbs');
$to_wbs = $this->input->post('to_wbs');
$position = $this->input->post('position');
$month = $this->input->post('month');
$id = $this->query_arr_trans($month);
$from_month = $id[0]->arr_from;
$to_month = $id[0]->arr_to;
$final_ids = array();
$process_mode = 3;
$created_on = date("Y-m-d h:i:s");
$all_ids = $this->input->post('all_ids');
if($position){
$pos_qry = 'AND position = "'.$position.'" ';
}
# Flag For checked input in the branch table.
if(!empty($prime_ids)){
$input_chk_update = 'UPDATE cw_arr_transactions SET input_check = CASE WHEN prime_arr_transactions_id IN ('.$prime_ids.') THEN 1 ELSE 0 END WHERE project_id = "'.$project_id.'" AND wbs_element = "'.$from_wbs.'" AND wbs_element = "'.$to_wbs.'" AND arr_from = "'.$from_month.'" AND arr_to = "'.$to_month.'" '.$pos_qry.' ';
$chk_result = $this->db->query("CALL sp_a_run ('RUN','$input_chk_update')");
}
# Records whose total earning,deduction,netpay zero should not be updated.
$input_qry = 'SELECT prime_arr_transactions_id FROM cw_arr_transactions WHERE trans_status = 1 AND total_earnings = 0 AND total_deductions = 0 AND net_pay = 0';
$input_info = $this->db->query("CALL sp_a_run ('SELECT','$input_qry')");
$input_rslt = $input_info->result_array();
$input_info->next_result();
foreach($input_rslt as $val){
foreach($all_ids as $subval){
$id = $val['prime_arr_transactions_id'];
if($id === $subval){
$final_ids[] = $subval;
}
}
}
$unique_ids = array_intersect_key($all_ids ?? [], $final_ids ?? []);
$final_ids = array_diff($all_ids ?? [], $final_ids ?? []);
$final_ids_str = implode('","', $final_ids ?? []);
$final_ids_str = '"'.$final_ids_str.'"';
if(empty($final_ids)){
$rslt = 'FALSE';
$msg = 'Update Failed. Total Earnings,Deductions,Netpay has 0 values.';
}
elseif($unique_ids && $final_ids){
$rslt = 'WARNING';
$msg = 'Update Successful. Some records were not submitted because they have 0 values in total earnings, total deductions, and net pay.';
}
else{
$rslt = 'TRUE';
$msg = 'Update Successful. All records have been successfully submitted.';
}
$entry_status_update = 'UPDATE cw_arr_transactions JOIN cw_arr_info on cw_arr_info.prime_arr_info_id = cw_arr_transactions.prime_arr_info_id SET cw_arr_info.entry_status = 1,cw_arr_transactions.arr_status = 2,cw_arr_transactions.entry_status = 1,cw_arr_transactions.check_status = 0,cw_arr_transactions.submitted_date = "'.$created_on.'",cw_arr_transactions.trans_updated_date = "'.$created_on.'",cw_arr_transactions.trans_updated_by = "'.$this->logged_id.'" WHERE cw_arr_transactions.prime_arr_transactions_id in('.$final_ids_str.')';
$result = $this->db->query("CALL sp_a_run ('RUN','$entry_status_update')");
if($result){
$tbl_data = $this->arrear_process_tbl($project_id,$from_wbs,$to_wbs,$from_month,$to_month,$position,$process_mode,$all_prime_id);
echo json_encode(array('success' => $rslt, 'message' => $msg,"table_data"=>$tbl_data));
}else{
echo json_encode(array('success' => false, 'message' => 'Entry status Not Updated'));
}
}
//update arr_adv input
public function update_arr_adv(){
$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);
}
$arr_val = $this->input->post('arr_val');
$arr_id = $this->input->post('arr_id');
$all_prime_id = $this->input->post('all_ids');
$all_ids = '"'.implode('","',$this->input->post('all_ids') ?? []).'"';
$month = $this->input->post('month');
$id = $this->query_arr_trans($month);
$from_date = $id[0]->arr_from;
$to_date = $id[0]->arr_to;
$project_id = "";
$from_wbs = "";
$to_wbs = "";
$position = "";
$created_on = date("Y-m-d h:i:s");
$arr_adv_upd = 'UPDATE cw_arr_transactions JOIN cw_arr_info on cw_arr_info.prime_arr_info_id = cw_arr_transactions.prime_arr_info_id SET cw_arr_transactions.arr_adv = "'.$arr_val.'",cw_arr_transactions.entry_status = 2,cw_arr_info.entry_status = 2,cw_arr_transactions.arr_status = 2,cw_arr_transactions.trans_updated_date = "'.$created_on.'",cw_arr_transactions.trans_updated_by= "'.$this->logged_id.'" WHERE prime_arr_transactions_id = "'.$arr_id.'" and cw_arr_transactions.trans_status =1';
$update_arr_adv_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$arr_adv_upd')");
if($update_arr_adv_rslt){
$tbl_data = $this->arrear_process_tbl($project_id,$from_wbs,$to_wbs,$from_date,$to_date,$position,$process_mode,$all_prime_id);
echo json_encode(array('success' => TRUE, 'message' => 'Arrear Advance successfully updated'));
}
}
//for approve or reject
public function checker_fun(){
$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);
}
$project_id = $this->input->post('project_id');
$from_wbs = $this->input->post('from_wbs');
$to_wbs = $this->input->post('to_wbs');
$position = $this->input->post('position');
$month = $this->input->post('month');
$id = $this->query_arr_trans($month);
$from_month = $id[0]->arr_from;
$to_month = $id[0]->arr_to;
$prime_ids = '"'.implode('","',$this->input->post('prime_ids') ?? []).'"';
$all_prime_id = $this->input->post('all_ids');
$all_ids = '"'.implode('","',$all_prime_id ?? []).'"';
$status = (int)$this->input->post('status');
$created_on = date("Y-m-d h:i:s");
if($position){
$position_qry = 'AND cw_arr_transactions.position = "'.$position.'" ';
}
//FOR ARREAR INFO
$chk_qry = 'SELECT DISTINCT prime_arr_info_id FROM cw_arr_transactions WHERE prime_arr_transactions_id IN ('.$prime_ids.')';
$chk_info = $this->db->query("CALL sp_a_run ('SELECT','$chk_qry')");
$chk_rslt = $chk_info->result();
$chk_info->next_result();
$prime_info_id = array_map(function($val){
return $val->prime_arr_info_id;
}, $chk_rslt ?? []);
$arr_info_id = '"'.implode('","',$prime_info_id ?? []).'"';
//UPDATE CHECK STATUS,APRROVE,REJECT DATE.
if($status === 1){
$process_mode = 4; // APPROVED
$audit_status_update = 'UPDATE cw_arr_info INNER JOIN cw_arr_transactions ON cw_arr_transactions.prime_arr_info_id = cw_arr_info.prime_arr_info_id SET cw_arr_transactions.arr_status = 1,cw_arr_info.arr_status = 1,cw_arr_transactions.check_status = 1,cw_arr_transactions.approved_date = "'.$created_on.'",cw_arr_transactions.trans_updated_by= "'.$this->logged_id.'" WHERE prime_arr_transactions_id IN ('.$prime_ids.')';
$result = $this->db->query("CALL sp_a_run ('UPDATE','$audit_status_update')");
# Update status.
$arr_sts_upd = 'UPDATE cw_arr_transactions SET arr_status = 4 WHERE prime_arr_transactions_id NOT IN ('.$prime_ids.') and prime_arr_info_id IN ('.$arr_info_id.') AND arr_status <> 1 AND project_id = "'.$project_id.'" AND wbs_element = "'.$from_wbs.'" AND wbs_element = "'.$to_wbs.'" AND arr_from = "'.$from_month.'" AND arr_to = "'.$to_month.'" '.$position_qry.' ';
$arr_sts_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$arr_sts_upd')");
if($result){
$tbl_data = $this->arrear_process_tbl($project_id,$from_wbs,$to_wbs,$from_date,$to_date,$position,'4',$all_prime_id);
echo json_encode(array('success' => TRUE, 'message' => 'Arrear successfully Approved!',"table_info" => $tbl_data));
}else{
echo json_encode(array('success' => FALSE, 'message' => 'Audit status Not Updated'));
}
}else{
$process_mode = 5; // REJECTED
$pos_qry = 'SELECT DISTINCT cw_arr_info.prime_arr_info_id FROM cw_arr_info JOIN cw_arr_transactions ON cw_arr_info.prime_arr_info_id = cw_arr_transactions.prime_arr_info_id WHERE cw_arr_info.arr_status = 1 AND cw_arr_info.trans_status = 1 AND prime_arr_transactions_id IN ('.$all_ids.')';
$pos_info = $this->db->query("CALL sp_a_run ('SELECT','$pos_qry')");
$pos_rslt = $pos_info->result();
$pos_info->next_result();
$reject_id = [];
foreach($pos_rslt as $val){
$reject_id[] = $val->prime_arr_info_id;
}
$reject_ids = '"'.implode('","', $reject_id ?? []).'"';
if($reject_id){
//UPDATE REJECTION STATUS FOR NEWLY PROCESSED RECORDS.
$reject_upd = 'UPDATE cw_arr_transactions INNER JOIN cw_arr_info ON cw_arr_transactions.prime_arr_info_id = cw_arr_info.prime_arr_info_id SET cw_arr_transactions.arr_status = 3,cw_arr_info.arr_status = 3,cw_arr_info.entry_status = 2,cw_arr_transactions.entry_status = 2,cw_arr_transactions.rejected_date = "'.$created_on.'" ,cw_arr_transactions.trans_updated_by= "'.$this->logged_id.'" WHERE cw_arr_info.prime_arr_info_id NOT IN ('.$reject_ids.') AND cw_arr_transactions.project_id = "'.$project_id.'" AND cw_arr_transactions.wbs_element = "'.$from_wbs.'" AND cw_arr_transactions.wbs_element = "'.$to_wbs.'" AND cw_arr_transactions.arr_from = "'.$from_month.'" AND cw_arr_transactions.arr_to = "'.$to_month.'" AND cw_arr_transactions.trans_status = 1 AND cw_arr_info.trans_status = 1 '.$position_qry.' ';
$reject_sts = $this->db->query("CALL sp_a_run ('UPDATE','$reject_upd')");
if($reject_sts){
$tbl_data = $this->arrear_process_tbl($project_id,$from_wbs,$to_wbs,$from_date,$to_date,$position,$process_mode,$all_prime_id);
echo json_encode(array('success' => FALSE, 'message' => 'Since some of the records have already been approved. Newly processed entry deleted.',"table_info" => $tbl_data));
exit(0);
}
}else{
//UPDATE STATUS FOR REJECTED RECORDS.
$audit_status_update = 'UPDATE cw_arr_info INNER JOIN cw_arr_transactions ON cw_arr_transactions.prime_arr_info_id = cw_arr_info.prime_arr_info_id SET cw_arr_info.arr_status = 3,cw_arr_transactions.arr_status = 3,cw_arr_info.entry_status = 2,cw_arr_transactions.entry_status = 2,cw_arr_transactions.rejected_date = "'.$created_on.'",cw_arr_transactions.trans_updated_by= "'.$this->logged_id.'" WHERE prime_arr_transactions_id IN ('.$all_ids.')';
$result = $this->db->query("CALL sp_a_run ('UPDATE','$audit_status_update')");
if($result){
echo json_encode(array('success' => TRUE, 'message' => 'Arrear Rejected Successfully'));
exit(0);
}
}
}
}
//FUNCTION FOR GET A ARREAR REPORT DATA ON 30NOV22 END
}
?>