File: /home/cafsindia/uds.cafsinfotech.in/application/controllers/Arrear_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Arrear_report extends Action_controller{
public function __construct(){
parent::__construct('arrear_report');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$this->page_info();
//PERSONAL AREA
$per_area_rslt = $this->query_build_function('personal_code,personal_name','cw_sap_personal_area','','trans_status = 1 and FIND_IN_SET(personal_code, "'.$this->logged_area_access.'") ');
//Generate list for Datalist
$per_area_list = "";
foreach($per_area_rslt as $for){
$personal_code = $for['personal_code'];
$personal_name = $for['personal_name'];
if($personal_code !== ""){
$per_area_list .= "<option data-value='".$personal_code."' value='".trim($personal_code)."' >".trim($personal_name)."</option>";
}
}
$data['per_area_list'] = $per_area_list;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//PROJECT ID GET BASED ON ARREAR INFO TABLE
public function get_personal_area(){
$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');
$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 cw_sap_project.pro_personal_area_id = "'.$personal_code.'" AND cw_arr_info.trans_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();
if($arr_pro_id_rslt){
echo json_encode(array("success"=>TRUE,"per_area"=>$arr_pro_id_rslt));
}else{
echo json_encode(array("success"=>FALSE,"message"=>"No project For this personal code.!"));
}
$data['project_list'] = $pro_id_list;
}
//FUNTION FOR WBS ELEMENT BASED ON PR0JECT ID
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');
$personal_code = $this->input->post('personal_code');
$get_wbs_qry = 'SELECT cw_sap_wbs.prime_sap_wbs_id,cw_sap_wbs.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 wbs_personal_area_id = "'.$personal_code.'" AND cw_arr_info.project_id = "'.$project_id.'" AND FIND_IN_SET(cw_sap_wbs.wbs_personal_area_id, "'.$this->logged_area_access.'") 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_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);
}
$personal_code = $this->input->post('personal_code');
$project_id = $this->input->post('project_id');
$wbs_element = $this->input->post('wbs_element');
//AAREAR TABLE REALTED POSITION START 11OCT22
$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_sap_wbs ON cw_sap_wbs.wbs_id = cw_arr_info.wbs_element WHERE cw_arr_info.trans_status = 1 AND cw_sap_position.trans_status = 1 AND wbs_personal_area_id = "'.$personal_code.'" AND cw_arr_info.project_id = "'.$project_id.'" AND cw_arr_info.wbs_element = "'.$wbs_element.'" 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>";
}
//AAREAR TABLE REALTED POSITION END 11OCT22
echo json_encode(array("success"=>true,"position_list"=>$pos_list));
}
public function report_rslt(){
$arr_status = (int)$this->input->post('arr_status');
$posting_month = $this->input->post('posting_month');
$per_area = $this->input->post('personal_code');
$project_id = $this->input->post('project_id');
$wbs_element = $this->input->post('wbs_element');
$position = $this->input->post('position');
$revised = $this->input->post('revised');
$diff = $this->input->post('diff');
$arrear = $this->input->post('arrear');
$exist = $this->input->post('exist');
$pos_month = $posting_month ? 'AND arr_posting_month = "'.$posting_month.'"' : '';
$per_qry = $per_area ? 'AND pro_personal_area_id = "'.$per_area.'"' : '';
$pro_qry = $project_id ? 'AND project_id = "'.$project_id.'"' : '';
$wbs_qry = $wbs_element ? 'AND wbs_element = "' .$wbs_element. '"' : '';
$pos_qry = $position ? 'AND position = "' .$position.'"' : '';
$arr_sts = $arr_status === 1 ? 'AND sap_trans_status = 2' : 'AND sap_trans_status <> 2';
# COLUMNS THAT I DONT WANT TO INCLUDE IN TABLE.
$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","submitted_date","entry_status","arr_from","arr_to","no_of_wrk_days","tot_ot_hrs","tot_ot_days","tot_ex_hrs","tot_ex_days","arr_posting_month","arr_gross")';
//,"diff_special_ot_rate_type","rev_ot_days_amt","diff_ot_days_amt","exist_ot_hrs_amt","rev_ot_hrs_amt","diff_ot_hrs_amt","exist_ex_hrs_amt","rev_ex_hrs_amt","diff_ex_hrs_amt","exist_ex_days_amt","rev_ex_days_amt","diff_ex_days_amt","exist_special_ot","diff_special_ot","rev_special_ot","exist_pd_spl","exist_ot_days_amt","diff_fspl","exist_pd_lwagepf","exist_uniform_struct","exist_pd_other","exist_pers_acci_insur_struct","exist_pd_wash","exist_pd_hra","exist_pd_lwage","exist_hal_special","exist_hal_wash","rev_pd_basic","rev_fvda","rev_pd_spl","rev_pd_lwagepf","rev_uniform_struct","rev_pd_other","rev_pers_acci_insur_struct","rev_pd_wash","rev_pd_hra","rev_pd_lwage","rev_hal_special","rev_hal_wash","diff_fvda","diff_pd_spl","diff_pd_lwagepf","diff_uniform_struct","diff_pd_other","diff_pers_acci_insur_struct","diff_pd_wash","diff_pd_hra","diff_pd_lwage","diff_hal_special","diff_hal_wash","termi_sts","it_dedn","ptax","exist_food_rate","rev_food_rate","diff_food_rate","exist_ybonus","rev_ybonus","diff_ybonus","exist_uniorm","rev_uniorm","diff_uniorm","arr_pd_basic","arr_fvda","arr_pd_spl","arr_pd_lwagepf","arr_uniform_struct","arr_pd_other","arr_pers_acci_insur_struct","arr_pd_wash","arr_pd_hra","arr_pd_lwage","arr_hal_special","arr_hal_wash","arr_food_rate","arr_uniorm","arr_ybonus","diff_pd_basic","arr_gross","arr_special_ot_rate_type","arr_spcl_ot_rate","exist_fvda","arr_ot_drate","arr_ot_hrate"
$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");
# ARR INFO
$arr_qry = 'SELECT * FROM cw_arr_transactions WHERE trans_status = 1';
$arr_info = $this->db->query("CALL sp_a_run ('SELECT','$arr_qry')");
$arr_rslt = $arr_info->result();
$arr_info->next_result();
$prime_info_id = [];
foreach($arr_rslt as $id){
$prime_info_id[$id->prime_arr_info_id] = $id;
}
# SAP TRANS STATUS
$sap_qry = 'SELECT * FROM cw_sap_trans_status WHERE trans_status = 1';
$sap_info = $this->db->query("CALL sp_a_run ('SELECT','$sap_qry')");
$sap_rslt = $sap_info->result();
$sap_info->next_result();
$sap_trans_arr = [];
foreach($sap_rslt as $val){
$prime_id = $val->prime_sap_trans_status_id;
$status = $val->sap_trans_status;
$sap_trans_arr[$prime_id] = $status;
}
# FOR GET MI EARNINGS AND DEDUCTION INPUTS NAME
$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");
#------------------------------------------ TABLE CREATION PROCESS STARTS ----------------------------------------#
$arr_qry = 'SELECT * FROM cw_arr_transactions JOIN cw_sap_project ON cw_arr_transactions.project_id = cw_sap_project.pro_id WHERE cw_arr_transactions.trans_status = 1 '.$arr_sts.' '.$pos_month.' '.$per_qry.' '.$pro_qry.' '.$wbs_qry.''.$pos_qry.'';
$arr_qry_info = $this->db->query("CALL sp_a_run ('SELECT','$arr_qry')");
$arr_qry_rslt = $arr_qry_info->result_array();
$arr_qry_info->next_result();
$min_from = null;
$max_to = null;
foreach($arr_qry_rslt as $date){
$arr_from = "01-" . $date["arr_from"];
$arr_to = "01-" . $date["arr_to"];
if($arr_from && $arr_to){
if($min_from === null || strtotime($arr_from) < strtotime($min_from)){
$min_from = $arr_from;
}
if($max_to === null || strtotime($arr_to) > strtotime($max_to)){
$max_to = $arr_to;
}
}
}
//THIS IS ARREAR RESULT TABLE CREATE ARRAY
$arr_rslt_arr = array();
array_walk($arr_qry_rslt, function($v, $k) use(&$arr_rslt_arr) {
$arr_rslt_arr[$v["prime_arr_transactions_id"]] = $v;
});
$arr_trans_id_arr = implode('","',array_map(function($v){
return $v['prime_arr_transactions_id'] = $v['prime_arr_transactions_id'];
}, $arr_qry_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){
echo json_encode(array('success' => FALSE, 'message' => 'No Records Found..!','table_info' =>''));
exit(0);
}
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'];
}
$table_head .= "<th>ARR FROM</th><th>ARR 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>";
$table_body = '';
$count = 1;
foreach($arr_rslt_arr as $val){
$arr_info_id = (int)$val["prime_arr_info_id"];
$emp_code = $val["employee_code"];
$emp_name = $val["employee_name"];
$arr_from = $val["arr_from"];
$arr_to = $val["arr_to"];
$termi_sts = $val["termi_sts"];
$arr_status = (int)$val["arr_status"];
$per_area = $val["pro_personal_area_id"];
$pos_no = $val["position"];
$pos_desc = $val["pos_desc"];
$rev_pos_no = $val["rev_position"];
$rev_pos_desc = $val["rev_pos_desc"];
$project = $val["project_desc"];
$wbs = $val["wbs_element"];
$wbs_desc = $val["wbs_desc"];
$utr_no = $val["utr_no"];
$submit_date = $val["submitted_date"];
$approve_date = $val["approved_date"];
$reject_date = $val["rejected_date"];
$sap_remarks = $val["sap_remarks"];
$check_status = (int)$val["check_status"];
$sap_doc_no = $val["sap_doc_no"];
$mb_doc_number = $val["mb_doc_number"];
$payment_doc_no = $val["payment_doc_number"];
$arr_posting_mon = $val["arr_posting_month"];
$sap_trans_status= (int)$val["sap_trans_status"];
$emp_sts = ((int)$termi_sts === 0) ? "Active" : "InActive";
$sap_trans_sts = $sap_trans_arr[$sap_trans_status] ? $sap_trans_arr[$sap_trans_status] : '-';
if($arr_status === 4){
$arr_sts = "Verified";
}elseif($arr_status === 1){
$arr_sts = "Approved";
}elseif($arr_status === 3){
$arr_sts = "Rejected";
}else{
$arr_sts = "Pending";
}
$arr_style = "style='color: " . (($arr_status === 4) ? "#00ABF0" : (($arr_status === 1) ? "green" : (($arr_status === 3) ? "red" : "orange"))) . "'";
$table_body .= "<tr><td>$arr_from</td><td>$arr_to</td><td $arr_style>$arr_sts</td><td style='text-align:center;'>$emp_code</td><td style='text-align:left;'>$emp_name</td><td>$emp_sts</td><td>$pos_no</td><td>$pos_desc</td><td style='text-align: center;'>$rev_pos_no</td><td>$rev_pos_desc</td><td>$project</td><td>$wbs</td><td>$wbs_desc</td>";
$trans_days_col_arr = array("ex_days","ex_hrs","ot_days","ot_hrs","pd","md");
# BASED ON CHECKBOX RESULT COLUMN DISPLAY.
foreach($pay_arr_col_arr as $pay_val){
$inp_head = strtoupper(str_replace("_", " ", $pay_val));
$inp_amt = $val[$pay_val] ?? 0.00;
$pay_str_arr = explode("_", $pay_val ?? "");
$pay_check = $pay_str_arr[0];
if(($exist && $pay_check === 'exist') || ($revised && $pay_check === 'rev') || ($diff && $pay_check === 'diff') || ($arrear && $pay_check === 'arr') || ($pay_check !== 'exist' && $pay_check !== 'rev' && $pay_check !== 'diff' && $pay_check !== 'arr')) {
if($count === 1){
$table_head .= "<th>$inp_head</th>";
}
$table_body .= "<td>$inp_amt</td>";
}
}
# ARREAR TRANSACTIONS DAYS INPUTS ADD STARTS.
if($min_from && $max_to){
$from_date = date("Y-m-d",strtotime($min_from));
$to_date = date("Y-m-t",strtotime($max_to));
$from_date_check = date("M", strtotime($from_month)) . date("y", strtotime($from_date));
$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);
}
$inp_mon_year_arr = array();
foreach($mi_inp_sel_arr as $inp_val){
foreach($mon_year_priod as $mon_val){
$inp_mon_year_arr[$inp_val."-".$mon_val] = $inp_val."-".$mon_val;
}
}
//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($val[$inp_mon_val]){
$inp_mon_year_val = $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"){
$inp_mon_year_val = $arr_input_rslt[$prime_id][$month_year][$input_name];
if($inp_mon_year_val <= 0){
$inp_mon_year_val = 0;
}
$table_body .= "<td>$inp_mon_year_val</td>";
}else{
$table_body .= "<td>$inp_mon_year_val</td>";
}
}
//MI INPUT NAME WITH SERACH MONTH AND YEAR BODY DATA CREATE END
$count++;
$utr_no = ($utr_no ? $utr_no : "-");
$sap_remarks = ($sap_remarks ? $sap_remarks : "-");
$mb_doc_number = ($mb_doc_number ? $mb_doc_number : "-");
$payment_doc_no = ($payment_doc_no ? $payment_doc_no : "-");
$sap_doc_no = ($sap_doc_no ? $sap_doc_no : "-");
$submitted_date = ($submit_date ? $submit_date : "-");
$approved_date = ($approve_date !== '0000-00-00 00:00:00' && $approve_date) ? $approve_date : "-";
$rejected_date = ($reject_date !== '0000-00-00 00:00:00' && $reject_date) ? $reject_date : "-";
$check_status = ($check_status === 1) ? 'Completed' : 'Pending';
$table_body .= "<td>$check_status</td><td>$sap_doc_no</td><td>$payment_doc_no</td><td>$mb_doc_number</td><td>$sap_trans_sts</td><td>$sap_remarks</td><td>$utr_no</td><td>$submitted_date</td><td>$approved_date</td><td>$rejected_date</td><td>$arr_posting_mon</td></tr>";
}
$table_head .= "<th>CHECK STATUS</th><th>SAP DOC NO</th><th>PAYMENT DOC NUMBER</th><th>MB DOC NUMBER</th><th>SAP TRANS STATUS</th><th>SAP REMARKS</th><th>UTR NO</th><th>SUBMITTED DATE</th><th>APPROVED DATE</th><th>REJECTED DATE</th><th>ARR POSTING MONTH</th>";
$table_info = "<table class='table table-hover' id='arr_report_table' style='white-space: nowrap;'><thead><tr>$table_head</tr></thead><tbody>$table_body</tbody></table>";
#------------------------------------------ TABLE CREATION PROCESS ENDS ----------------------------------------#
if($arr_qry_rslt){
echo json_encode(array('success' => TRUE, 'table_info' => $table_info));
}else{
echo json_encode(array('success' => FALSE, 'message'=>'No records found.', 'table_info' => $table_info));
}
}
}
?>